Allow zero or null values in foreign key columns? 2

[fsteeman]

Maybe I sound like a heretic, but when explicitly adding relationship constraints to the database, is it possible in SQL SERVER (I am using Management Studio Express) to allow a foreign key to have null values or values that can not be found in the related table (like 'zero')?

Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[SQLBill]

Nope. If it allowed duplicate values (multiple NULLs or 0's) then the column wouldn't be able to function as a 'KEY' column (Primary or Foreign). Plus foreign keys must match a Primary Key column. Primary Keys must be unique - ergo Foreign Keys must be unique.

-SQLBill

Posting advice: FAQ481-4875

 

[fsteeman]

Of course I understand, but yet it is possible to use the foreign keys as such when using a view with a join on the foreign key field. The non-matching records are simply not displayed. Why isn't it possible to define a relaxed constraint that stores information about the relationship, but allows it to refer to none of the records in the foreign table?

Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[bborissov]

Why you need that? There is something wrong with design then. Why not just store these records with non matching "KEYs" in separate table?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[unclejimbob]

Not so fast. Yes it IS possible to have a foreign key that allows NULL values, however you can't specify it to allow values that do not exist in the related table. Foreign keys do not have to match a primary key, just a unique key.

Having NULL values in an FK is not a good idea because it defeats the whole purpose of defining them in the first place.

I agree with Borslav, if the FK is used to indicate a 'loosely coupled' relationship then its not a foreign key, it should be modelled separately - as an associative entity perhaps ?

have a good one

 

[fsteeman]

Thanks, but I believe there is nothing wrong with my design. I simply use 'reference columns' in one table to refer to records in another table. There are two reasons why I am interested in zero values (or null values if there is no choice) in these 'reference columns':

1. It refers to a record in another table that can be optionally, but necessarily, specified.

2. It refers to a parent record in the same table as to store record hierarchy as adjacency. The records in the top level of the hierarchy, which have no parent, have zero as value in this column.

To me this seems like a totally valid and working construction. However, I am open to devastating criticism by the experts, of course...

Cheers,

Fedor


Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[r937]

absolutely nothing wrong with NULLs in a foreign key column -- perfectly valid design

parent_id at the top of a hierarchy should be NULL, not 0

r937.com | rudy.ca

 

[vbkris]

hi guys,

i too have another peculiar doubt.

Can you define multiple FKs on a field. Lets say a particular detail can be updated by 2 types of users (belonging to 2 different tables) and i want to enforce a relationship here (using a simple PK FK relationship), is there a way???

Known is handfull, Unknown is worldfull

 

[r937]

yes you can have multiple FKs on a column

r937.com | rudy.ca

 

[vbkris]

thanks, will try that out and let you know the outcome...

Known is handfull, Unknown is worldfull

 

[fsteeman]

Thanks, r937! So all I need to do is to make sure that unspecified foreign key values are null and not zero (or some other value not present in the foreign table).

Fedor Steeman
Geological Museum Copenhagen
Denmark

 

[unclejimbob]

absolutely nothing wrong with NULLs in a foreign key column -- perfectly valid design"
Um, ouch; I guess I come from the Joe Celko school of FK design

For me NULLS are used for two things, stuff I don't know about something and stuff that isn't applicable.

Examples:

date of birth - people may not want to supply this information (don't know)
number of cervical scans - only applies to females (not applicable)

I generally try to avoid using NULLs in FKs because it doesn't fit with my view of the relational model.

If you take the 'NULLs in FK constraints' to the nth degree you could consider it as having

Case 1 - a single row containing 100 columns, all NULLable and all FKs referencing 100 other tables.

Case 2 - Or you could have a single row with no FKs and 100 associative tables.

This all becomes jolly good fun when you have both 1..n and n..n relationships.

In the first case you implement 1..n relationships one way and n..n relationships another. Oops.

In the second case you implement relationships using the same method.

I won't even go into how a good data modelling tool reacts when presented with Case 1.

In the case of a BOM relationship then I guess you have no choice, in the other case you do.

My feeling is if the design works and you are happy with it then good luck to you, if however you
are finding that you have to jump through hoops in order to get the job done then there is
something wrong with it.

The fact that you are having to question what values to put into the FK column in order to
avoid problems looks suspiciously like a hoop to me.

I happen to use FK constraints in this way, for example a design decision to keep the complexity
of the database to a minimum for reporting purposes - however just because I deliberately choose
to do it doesn't make it right in all cases.

My 2c worth.

 

[r937]

neither "date of birth" nor "number of cervical scans" really falls into the "NULL FK" category, do they (because they're not really FKs, are they)

NULL FK = not applicable

yes, you can go ahead and partition the FK column off into a separate table if it makes you feel more relational

i would not consider a NULL FK as "jumping through hoops"







r937.com | rudy.ca

 

[unclejimbob]

neither "date of birth" nor "number of cervical scans" really falls into the "NULL FK" category, do they (because they're not really FKs, are they)

That's true, although I'm not sure how you made the leap from my proposing that these two attributes were
candidates for NULLability to being candidates for foreign keys.

NULL FK = not applicable
NULL FK = lowest common denominator approach

yes, you can go ahead and partition the FK column off into a separate table if it makes you feel more relational
I don't carry out my data modelling decisions based upon how 'relational' I feel, but upon theory, business, and
technical reasons - some of which I have already outlined and some of which show both the pros and cons of this approach.

Perhaps you could put forward some similar reasonings.

i would not consider a NULL FK as "jumping through hoops"
True, although I never said this either, what I said was that in _this particular case_ it looks as though it is and that a NULL FK is a 'one size fits all' approach.

To put it into perspective I essentially model relationships in the following way (and I assume everyone else on the planet has some sort of variation on these):

1) Is is appropriate to model the relationship as a separate entity Yes/No
2) Is it appropriate to model the relationship by replacing what would normally be a NULL with a 'unknown'
for example FKs into lookup tables that have a 'N/A','unknown' or 'None' tuple Yes/No
3) Is it appropriate to model the relationship as a NULLable attribute Yes/No

95% of the data I model falls into the first 2 categories without any major implementation hassles.

I guess the only difference between us is that your first choice just happens to be my last resort.

Have a good one, right, I'm off to the beach.

ujb

 

[danvlas]

Null FK is OK. I have an example of it from real steel industry:
They cast ingots that have to be entered in the database (production for that day). But they know what steel grade they obtained only after the metal cools down (4-5 days later). The steel grades provide the primary key from the grades table.
As such, they enter the production with a Null FK, then allocate an exixting value when they know it.
There are many examples in real life...

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[unclejimbob]

At the risk of repeating myself, no, in my opinion its not automatically OK to present this as a
jolly spiffing idea

I consider that it is OK to do this once all the other possibilities have been examined.

In your example I guess you could just have used a steel grade of 'Unknown' and added it to the lookup
table - i.e. a key/value pair, no more NULLs.

This has the added advantage of being able to distinguish WHY you don't know the value, for
example what happens if the steel grade is incorrectly assessed ?

Then the NULL can be replaced with a more useful set of descriptions:
'Not yet known - awaiting cooling'
'Not yet known - incorrectly graded' etc

Yes there are many examples in real life, unfortunately they seem to be frequently modelled incorrectly.

Here is a list of cons and examples I have come up with just in the last 15 minutes:
a) dealing with how other users handle result sets that can contain a FK NULL, e.g. developers, as a rule I
try to make things as uncomplicated as possible so if they have a list of descriptors that they pick up from
a related lookup table and they find a NULL value what do they display in a drop list pick list - PANIC STATIONS!

F->Female
M->Male
NULL->???

or

F->Female
M->Male
U->Unknown

So, I give them a 'None' or 'Unknown' and they feel that the world is still a place they understand.

b) middle-ware design tools that expect a column to hold a value that always exists in a related table - this
can be a bit of a pain

c) concurrency issues - OK I'm this is more to do with the database design itself rather than any issue with
NULLs in FKs so its a bit of a stretch, still if you imagine two NULLable FKs and replace them with relations
you won't have any locking or concurrency issues that you would get if you had two users trying to update
different NULL FK columns on the same row

d) tricky reporting - just as long as you accept the fact that in the case of 'lookups' you have to always
remember to left join a nullable FK column if you want to see all the members of a set irrespective of what
their related values are, e.g. show all doctors and their sex doctors in one table, sex descriptors in another:

Using a right join you get:

Fred -> Male
Joe -> Male
Eileeen -> Female
Chris -> NULL (doesn't show because no related row in sex table)

This becomes jolly good fun when you have 5 or 6 NULLable FKs, like to see the performance hit on my
database with a query containing 6 left joins anyone ?

Pros include:
a) ease of reporting - don't have to worry about including lots of tables
b) ease of initial modelling

I'm sure if you think about it you can come up with some that apply in your case.

OK this thread is now probably WAY out of what the forum really allows so this is the last post from
me on the subject.

See you in the other forums my friends, I'm off to bed.

 

[jbenson001]

UncleJimBob.. excellent points.. I agree with you.. I avoid using NULLs anywhere when possible.... especially in a FK

A star for you...

Jim

 

[harebrain]

2. It refers to a parent record in the same table as to store record hierarchy as adjacency. The records in the top level of the hierarchy, which have no parent, have zero as value in this column.

(Null rather than zero would be appropriate here.) Question for the experts: how else would you model a reflexive join?

 

[danvlas]

So, you would replace 'Unknown' with 'Virtually unknown, but specified'.
I don't see the difference between the two, moreover I could say that it's an additional burden for the user or programmer to enter and manage another value instead of accepting the reality: I don't know what I should enter, why would I enter anything? You may forget to enter that record. Somehow, any record can be deleted. In a complex ERP you will have hundreds of relationships. Can you manage an extra record for any table just to replace the Null?
For me, the possibility to leave some values unallocated represents a facility. It's an open discussion and everyone does what they feel appropriate. But for myself, Null remains the value to have when I don't know the real value at that moment.

Just a thought...


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[r937]

daniel, you are not alone



r937.com | rudy.ca