Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30

    Unanswered: Child table referring to a unique key

    A child table can refer to either a primary or unique key. Let's consider the 2nd case, where we have a foreign key relating to a unique key in the parent table. We update one of the parent table's rows, making the unique key column NULL - what happens to a row, in the child table, that was referring to the old non-NULL unique key ? What happens to the integrity in this relation, knowing that unique keys can contain NULL values and that foreign keys can refer to one ? This is likely to be a classic question, but I could not quickly find the answer for it.

    Thanks for the prompt responses.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    foreign keys cannot refer to a null value

    but they can be null

    subtle difference, eh?

    in any case, if you update the unique key, the action taken on the foreign key depends entirely on what you've defined in the ON UPDATE clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2003
    Location
    Brazil
    Posts
    30
    Thanks for your reply, but my issue still exists. I knew foreign keys can be null, but you said something I did not know: they CANNOT refer to null values. This goes together with what I thought would be reasonable, but the issue is:

    Unique keys definition: Ensure no duplicate values exist for that column. MAY HAVE NULL VALUES.

    Foreign key definition: Enforces a relation w... bla bla bla... here's what is troubling me, together with the caps above: MAY REFER TO EITHER PRIMARY OR UNIQUE KEYS.

    So... I want to say "No, foreign keys cannot refer to NULL values", but, if my definitions are accurate, isn't it possible to have a relation between a Unique and a foreign key where... the Unique key is, at least, updated for given rows, receiveing NULL values...
    So now the foreign key, which was refering to non-NULL values, is referring to these null values - because the relation was based upon a Unique key in the parent table, and a unique key accepts NULL.

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Oracle would prevent you from changing the parent key to NULL, if you had a foreign key constraint in place (unless you've defined the foreign key constraint to either DELETE the child records, or convert their foreign key values to NULL).

    ---=Chuck

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    okay. i think what you have to do, 435, is concentrate on what you want and not on all the things that are possible, because there are many choices for what's possible
    So... I want to say "No, foreign keys cannot refer to NULL values"
    excellent, then declare the foreign key as NOT NULL

    end of

    now, should you decide to change a row in the parent table and set its unique key to null, and there's a foreign key pointing to it, then the database will not let you

    simple, eh?

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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