Why can you have NULL in Foreign Key?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Why can you have NULL in Foreign Key?

Hybrid View

  1. #1
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346

    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??
    Last edited by Horace; 12-06-2004 at 10:06 AM.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    "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.

  3. #3
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    So like RDBMS without the 'R'......

  4. #4
    Join Date
    Mar 2002
    Posts
    534
    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.

  5. #5
    Join Date
    May 2001
    Posts
    736
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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]
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  8. #8
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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!

  9. #9
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    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.

  10. #10
    Join Date
    Dec 1999
    Location
    Purgatory
    Posts
    346
    Yeah, I know.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width