aspose file tools*
The moose likes Java in General and the fly likes NULL values in FK field Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "NULL values in FK field" Watch "NULL values in FK field" New topic
Author

NULL values in FK field

Rodrigo Bossini
Ranch Hand

Joined: Jul 03, 2009
Posts: 113
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?


I see wind mills
Md Ibrahim
Greenhorn

Joined: Mar 24, 2010
Posts: 14
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

Joined: Jul 03, 2009
Posts: 113
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

Joined: Mar 24, 2010
Posts: 14
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

Joined: Jul 03, 2009
Posts: 113
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

Joined: Mar 24, 2010
Posts: 14
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

Joined: Oct 27, 2005
Posts: 19693
    
  20

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?


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
Rodrigo Bossini
Ranch Hand

Joined: Jul 03, 2009
Posts: 113
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

Joined: Oct 27, 2005
Posts: 19693
    
  20

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

Joined: Jul 03, 2009
Posts: 113
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: NULL values in FK field