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??
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://22.214.171.124/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.
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.