• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

NULL values in FK field

 
Rodrigo Bossini
Ranch Hand
Posts: 113
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Conceptually, is it wrong to allow a foreign key field to have NULL values?
Say I have a 0 x N model.

Say a table Client and a table Bag.

A Bag may exist by itself or may belong to a Client.

How do I model it? DO I add an idClient field to the bag table and allow it to have null values or what?
 
Md Ibrahim
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Rodrigo,


you can do it this way


Table BAG : bagID (PK)
and other bag related attribute fields....

Table Client: ClientID(PK) -- note we are not referring this anywhere so its not FK here.
bagID (FK)


now here you can get the Client information using bagID....

And ofcourse there may be many more ways of doing it, i just posted AFAIK.

Do write back if this helps you. take care


BY,
Ibrahim
 
Rodrigo Bossini
Ranch Hand
Posts: 113
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Md Ibrahim wrote:Hi Rodrigo,


you can do it this way


Table BAG : bagID (PK)
and other bag related attribute fields....

Table Client: ClientID(PK) -- note we are not referring this anywhere so its not FK here.
bagID (FK)


now here you can get the Client information using bagID....

And ofcourse there may be many more ways of doing it, i just posted AFAIK.

Do write back if this helps you. take care


BY,
Ibrahim


Are you telling me to add the bagId to the Client table? What if I want one client to have multiple bags? How would that work?
 
Md Ibrahim
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, If you want one client to have multiple bags, keep adding new entries in Client table with same ClientID but with Different BagID
 
Rodrigo Bossini
Ranch Hand
Posts: 113
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Md Ibrahim wrote:yes, If you want one client to have multiple bags, keep adding new entries in Client table with same ClientID but with Different BagID


Nope. Conceptually totally wrong. Client data will be redundant. Wrong. Also you said clientID would be an PK. How could I possibly "keep adding new entries with the same clientID" then ???

I'm waiting for new replies from more experienced users.
 
Md Ibrahim
Greenhorn
Posts: 14
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ya sorry for that, that will not work out as you told


Ok You have Client informations in Client Table (not duplicated), and now you have Bag informations
as a static information in some table (say BAG_STATICDATA), Only when the client wants to add a bag to his account
you will insert an ENTRY in BAG table with this Client's ID (this is how most of real time apps works).
You will have some static data table (say BAG_STATICDATA )
from where you can get the information with the BagID filed as FK

so the final structure will be like this

CLIENT:

clientid
other client infos....

BAG_STATICDATA:

BagID (PK) --- unique id
all bag infos..

CLIENT_BAG_DETAILS:

BagID :
ClientID: This is not PK here---this is FK (can have duplicates but not null)

This shud be fine, Because here there is no possibility of inserting NULL value in BagID or CLientID
and One CLientID can have multiple BagIDs in CLIENT_BAG_DETAILS table.
now retrieve the BagInfo from BAG_STATICDATA table with BagID..




By,
Ibrahim
 
Rob Spoor
Sheriff
Pie
Posts: 20493
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rodrigo Bossini wrote:Conceptually, is it wrong to allow a foreign key field to have NULL values?

Only if that foreign key should be required. If not then no.

Consider a tree-based table, where a table has a foreign key to itself. This foreign key represents the parent in the tree. If a NULL value would not be allowed then how would you represent the root of the tree?
 
Rodrigo Bossini
Ranch Hand
Posts: 113
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rob Prime wrote:
Rodrigo Bossini wrote:Conceptually, is it wrong to allow a foreign key field to have NULL values?

Only if that foreign key should be required. If not then no.

Consider a tree-based table, where a table has a foreign key to itself. This foreign key represents the parent in the tree. If a NULL value would not be allowed then how would you represent the root of the tree?


Can you give me a simple example on which a table needs to reference itself and show on this example why a null value would be required?
 
Rob Spoor
Sheriff
Pie
Posts: 20493
54
Chrome Eclipse IDE Java Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ID: int
Name: varchar(255)
ParentID: int

ParentID is a fkey to the same table. For instance, these records
would for this tree
As you can see, the root node needs a NULL ParentID, or else it wouldn't be the root node.
 
Rodrigo Bossini
Ranch Hand
Posts: 113
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Rob Prime wrote:ID: int
Name: varchar(255)
ParentID: int

ParentID is a fkey to the same table. For instance, these records
would for this tree
As you can see, the root node needs a NULL ParentID, or else it wouldn't be the root node.


That was great. Thank you.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic