Why can you have NULL in Foreign Key?

Okay, no flaming!! Remember it's the season of goodwill!

Why does Oracle allow NULL to be inserted into a foreign key column?

I've read the CONCEPTS manual again (so no RTFM please) - "Foreign Key is an integrity constraint that requires each value in a column or set of columns in a child table, to match a value in a related parent table's UNIQUE or PRIMARY KEY. The relational model permits the value of foreign keys either to match the referenced primary or unique key value, or be null. If any column of a composite foreign key is null, then the non-null portions of the key do not have to match any corresponding portion of a parent key."

If it's supposed to be an integrity constraint, why does it allow you to create orphan child records??

"Why?" is always a good question.

I suppose it gives you a choice:
FK: child can't point to non-existant parent, but can be an orphan.
NOT NULL FK: child can't point to non-existant parent, and can't be an orphan.

Not very profound, I must admit.

So like RDBMS without the 'R'......

Quote:

---

Originally posted by Horace
So like RDBMS without the 'R'......

---

I don't think so, may be there are some cases where there is simply no relation with this other table.
And in case there always should be a relation just use the NOT NULL constraint.

There are some cases where child table is permitted to have a orphan.This depends on the business requirement and it is clearly defined.The following link will show some cases where u can have this.
http://216.239.59.104/search?q=cache...ll+value&hl=en

Quote:

---

Originally posted by Horace
So like RDBMS without the 'R'......

---

[geek-mode]

I believe that the term "Relational" actually refers to the tables, or the result set from a query (logically the same thing), not the joins between them.

[/geek-mode]

I think the geek is right: http://www.15seconds.com/issue/020522.htm

I guess we'd better be strict about calling a FK a relationship.

Whilst were on the subject, could anyone suggest a definitive text for RDBMS design and data modelling? Looking to fill a gap on my manual shelf!

Quote:

---

Originally posted by akhadar
There are some cases where child table is permitted to have a orphan.This depends on the business requirement and it is clearly defined.The following link will show some cases where u can have this.
http://216.239.59.104/search?q=cache...ll+value&hl=en

---

I agree - its what the business model requires. The value may be null as at the time the record is created as its unknown, or not aplicable at that stage. But when a value is entered then it must be a value in the parent table. The nullable PK-FK relationship is the best way to implement this I feel; rather than using application code or a DB trigger do the checking.

example: Help Desk records calls from a user:

Not Nullable fields might be: Call Id, UserName, Call Description, Priority... etc

The 'Allocated to' field may not be known when the call is entered, but when the Call is assigned to a technician then the value must match a valid value in 'Technicians'. A nullable FK is ideal for this.

Alternatively - add a 'Not Known' row in Technicians and make the FK field NOT NULL.

Yeah, I know.....

Quote:

---

Originally posted by Horace
Yeah, I know.....

---

want that one

Sorry - was that patronising?

I was trying to add a real-world explanation for any newbies browsing.



:confused:

No, not patronising, I can tell you're not a fan of TV series 'Little Britain'. It was a joke............












I'll get me coat.......

Using the classic emp/dept tables - an employee has been hired but not yet assigned a dept !!

:cool:

Quote:

---

Originally posted by Horace
I'll get me coat.......

---

Get yer knickers on and make me a cup of tea.