Results 1 to 9 of 9
  1. #1
    Join Date
    Feb 2010
    Posts
    75

    Unanswered: Foreign Key not enforced like PK

    Easy question here.

    I have a table T1 with a unique primary key ID. So if you try to insert 2 identical records, you cant because the IDs need to be different.

    I have a second table T2 which is related to T1. T2 has a unique primary key ID as well, and also has another field called T1ID

    I *think* I have set up a foreign key from T2.T1ID to T1.ID, but its not behaving the way I want. T2 is allowing me to insert duplicate records, even though T1 does not.

    Is there a way to make the 'properties' of T1.ID inherited through the foreign key, to T2.T1ID ??

    So for example if T1.ID prevents duplicate records in T1 then T2.T1ID should prevent duplicate records in T2.

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by BeerOclock View Post
    I *think* I have set up a foreign key from T2.T1ID to T1.ID, but its not behaving the way I want. T2 is allowing me to insert duplicate records, even though T1 does not.
    That's the way it should be. A referential integrity constraint ensures that the values exist in the parent table, not that they are unique.

    Quote Originally Posted by BeerOclock View Post
    Is there a way to make the 'properties' of T1.ID inherited through the foreign key, to T2.T1ID ??

    So for example if T1.ID prevents duplicate records in T1 then T2.T1ID should prevent duplicate records in T2.
    Only if you define a unique constraint on T2.T1ID, in addition to the foreign key.
    ---
    "It does not work" is not a valid problem statement.

  3. #3
    Join Date
    Feb 2010
    Posts
    75
    I knew that.



  4. #4
    Join Date
    Feb 2010
    Posts
    75
    I knew that.



  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You seem to be implementing a 1:1 or 1:0/1 relationship between the tables, which I find useless at best and annoying at worst. But if you insist on it then add a unique constraint to the column on the second table.
    If it's not practically useful, then it's practically useless.

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

  6. #6
    Join Date
    Feb 2010
    Posts
    75
    Sorry, my terminology is a bit rusty and I forget what is meant by "1:1, 1:0/1 relationship between tables"

    Basically whats happening is I have a large table that I'm trying to split in 2, vertically. In other words, some columns stay in Table1, while others move to Table2. Logically they are kinda the same table, thats why if Table1 shouldnt have duplicates, then neither shoudl Table2. I know thats a wierd thing to do, (and maybe thats what you meant by 1:1 ??). But my reason is that Table1 is a master table, that will reside in a central database, while Table2 is sort of like a 'more details' table, and will be copied to each client database with different info. Table1 is big and not needed on each client so it makes sense to do the split (I think).

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That is 1:1.
    You know you can't have foreign keys across databases, right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    also, since its one to one, why use a different key? why not keep the key the same for simplicity and understanding?
    Dave

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Exactly what I tried to post, until the forum server went bonkers.
    Just use the same primary key in both tables.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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