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

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

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

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

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

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

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

yes you can have multiple FKs on a column

r937.com | rudy.ca

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

Known is handfull, Unknown is worldfull

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

"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.

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

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

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

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.

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

A star for you...

Jim

Quote (Fedor):

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?

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

daniel, you are not alone

r937.com | rudy.ca

Thanks Rudy, I know :) . To be completely honest, I learnt this the hard way.

Daniel Vlas
Systems Consultant

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

Just looking back at some of my replies and came across this one. As the questions are posed I figure I may as well answer them

"So, you would replace 'Unknown' with 'Virtually unknown, but specified'.
Heck, yes - but see my other comments above for provisos and disclaimers

"I don't see the difference between the two.."
Its subtle but its there, one way means...

"I don't know the value for some reason"

and the other means...

"I don't know the value but I know the reason why I don't know"

In my business, in fact I would say that in ANY business that depends upon reporting to some extent this is crucial, why let the piece of information that you don't know a value (in your case, because it is time dependent), reside in your head or on a bit of paper or as 'general knowledge known inside a company' when you can have it reside in a repository for _everyone_ to understand ?

"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?"
Its not if you use a default...which is what I usually do,
my programmers never have to wonder what, if anything, they need to put into a column, once they see it has a default they know they do not have to worry about it - and all my lookups have defaults

"You may forget to enter that record. Somehow, any record can be deleted."
If you have physically modelled your database to reflect the relationship then again you would expect the row to exist, 'forgetting to enter the record' is simply not an option, after all it IS a relationship. All a NULL says to me is that sometimes its treated as a relationship and sometimes its not - which I personally find unacceptable.

In a complex ERP you will have hundreds of relationships. Can you manage an extra record for any table just to replace the Null?"
Heck yes (and I do), although in this particular case remember that we are not talking about _all_ relationships (for example  associative entity types/many-to many), just those that support relationships to 'lookup-type' tables

"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."

Fair enough. Here is an example that follows on from your situation:

Your manager asks a new IT employee to write a report that shows a summary of all ingots created by grade.

Here is the summary data...

Grade 1 10 ingots
Grade 2 20 ingots
Not graded 100 ingots

Simple, right ?

So they create the report joining each instance of an ingot to the grade table and find that they have two groups Grade 1 and Grade 2 with a total of 30 ingots.

Oh dear.

Not a probem I hear you say, you simply left join the ingots to the grade table and voila.

So your report now looks like this:

Grade 1 10
Grade 2 20
null(or however your reporting tool represents an unknown value) 100

Not very nice looking is it ?

No problem, you just change the report with some code to turn 'null' into the string 'Not yet graded' which gives you this:

Grade 1 10
Grade 2 20
Not yet graded 100

Congratulations, you've just side-stepped your RDBMS and put a piece of valuable information into external storage.

But wait, there's more and it gets even better...

Now what happens is that another manager doesn't like the term 'Not yet graded' - and as the column is NULL he has no clue as to what would be an appropriate value - he wants to see 'Ungraded', so you create another report (or modify the existing one and add yet more code) to handle this.

Now you have the same piece of information being referenced in two separate ways, bring on the battle of the printouts and of course the auditors will have a field day

I too learnt this the hard way - never again.

Doesn't it make more sense to simply have an agreement across the organization that an ungraded ingot with have a grade of 'Ungraded' rather than 'any-old thing-we-can-dream-up-on-the-spur-of-the-moment' ?

For me the ability add a single record to a table in order to avoid having to answer the same question ("Under what circumstances would we not know the grading of the ingot ?") multiple times from newbies to my organization is in itself a saving, not to mention my suppliers, customers, employees and auditors all referring to the same piece of non-knowledge by the same terminology is a real bonus. I know it sounds funny, but believe me, in my situation I would much prefer to be able to report in a consistent, standard and meaningful way the status of an object - even if the status is 'I don't know the status'



happy days all
ujb