Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2003
    Location
    Romania
    Posts
    8

    Unanswered: Could not create a Foreign key on 2 unique columns

    Hello.
    Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.

    I have:
    CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)

    CREATE TABLE T_FK (ID1 INT, ID2 INT)

    And I want to do:

    ALTER TABLE T_FK ADD CONSTRAINT CHK_FK FOREIGN KEY (ID1, ID2) REFERENCES T_PK (ID1,ID2)

    I see no reason why this is not working because always
    a row in the table T_FK referencing only one row in table T_PK.

    Thank you.
    Have a nice day.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    in order for a compound FK to be declared, it must reference a compound unique key

    in other words, it's not enough that ID1 and ID2 are each unique, the pair of them must be unique too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    The bigger questions are why do you have TWO unique columns in a single table, and why would you feel the need to have TWO foreign keys to the same unique record?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Jul 2003
    Location
    Romania
    Posts
    8
    Thank you for you reply.
    Yes, I know that in MS Server documentation said those columns in FK must refer to A unique constraint.

    My question was “why is not possible..” because mathematical it is possible to unique identify a row in table T_FK with a row in table T_PK.
    I put such a question only from this logical point of view not for a need of use..
    and maybe an answer could be you first question… Practically it’s no need to have 2 unique columns in the same table.

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    why can a compound FK not reference two separately-unique-but-perhaps-invalid-when-combined keys?

    becuase of the possibility that together, they might be invalid, i.e. not exist

    a FK must refer to a unique row (this is relational integrity), and a unique row must be identifiable by a unique key (this is entity integrity)

    if ID1 and ID2 are separately unique, then table1 could have rows like this --

    ID1 ID2
    101 205
    102 206
    103 207
    104 208

    your FK definition is not valid because it would allow {101,208} as a FK which doesn't exist as a PK, even though 101 and 208 do exist and are separately unique
    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
  •