Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2010
    Posts
    7

    Unanswered: Is there any difference between these two foreign key query?

    Is there any difference between these two foreign key query?

    Code:
    CONSTRAINT test FOREIGN KEY (abc, xyz)
          REFERENCES test2 (abc, xyz),
    vs.
    Code:
    CONSTRAINT test1 FOREIGN KEY abc
          REFERENCES test2 abc,
    CONSTRAINT test2 FOREIGN KEY xyz
          REFERENCES test2 xyz

  2. #2
    Join Date
    Aug 2010
    Posts
    7

    Is there any difference between these two foreign key query?

    Is there any difference between these two foreign key query?

    Code:
    CONSTRAINT test FOREIGN KEY (abc, xyz)
          REFERENCES test2 (abc, xyz),
    vs.
    Code:
    CONSTRAINT test1 FOREIGN KEY abc
          REFERENCES test2 abc,
    CONSTRAINT test2 FOREIGN KEY xyz
          REFERENCES test2 xyz

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cpthk View Post
    Is there any difference between these two foreign key query?
    yes, sir, absolutely there is a big difference

    please do a SHOW CREATE TABLE for test2 and i'll explain why

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

  4. #4
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    If the primary key of test2 is defined as (abc, xyz) then you cannot create foreign keys that point to only one of the columns.

    So the second statement will not work.

  5. #5
    Join Date
    Aug 2010
    Posts
    7
    sorry, I do not have that in my actual database. I am just asking because I saw other's query.

  6. #6
    Join Date
    Aug 2010
    Posts
    7
    so you mean the first code will check if that test2 table has a row that has both abc and xyz as values?
    and second code will check separately that test2 table a row that has abc as value, and test2 table has a row that xyz as values.

    In short, first code, both values has to exist in the same row, second code can be separated?

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cpthk View Post
    sorry, I do not have that in my actual database. I am just asking because I saw other's query.
    okay, i understand

    after you actually work with foreign keys for a while, you will understand why those two are different

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

  8. #8
    Join Date
    Aug 2010
    Posts
    7
    the first code will check if that test2 table has a row that has both abc and xyz as values?
    and second code will check separately that test2 table a row that has abc as value, and test2 table has a row that xyz as values.

    In short, first code, both values has to exist in the same row, second code can be separated? Is this correct?

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cpthk View Post
    so you mean the first code will check if that test2 table has a row that has both abc and xyz as values?
    and second code will check separately that test2 table a row that has abc as value, and test2 table has a row that xyz as values.

    In short, first code, both values has to exist in the same row, second code can be separated?
    that is correct

    i find it very useful to think of the implications about test2 that the FKs give us, as opposed to the process-oriented checking of test1 values existing in test2

    every FK must reference a unique key (wherekey is not an index, but rather a defining characteristic, which is uniqueness)

    a table might have several candidate keys, and one of them is (usually) selected to be the primary key

    so now let's review the last example ...

    CONSTRAINT test1 FOREIGN KEY abc REFERENCES test2 abc,
    CONSTRAINT test2 FOREIGN KEY xyz REFERENCES test2 xyz

    we know that abc will be unique in test2, so let's say that there are N values

    but we also know that xyz will be unique in test2, so therefore there will also be N values of xyz

    conclusion: the design of test1 implied by these two FKs simultaneously (note the comma!) is that in order to insert a row into test1, you have to supply both key values simultaneously, when in fact giving only one of them is all that is required to determine which row of test2 the row being inserted in test1 belongs to (where you can replace "belongs to" with appropriate terminology for your particular application)

    whether it is desirable for an application to have specify redundant key values is a separate question

    in the first example ...

    CONSTRAINT test FOREIGN KEY (abc, xyz) REFERENCES test2 (abc, xyz)

    if we assumpe this is a different test2 table from the above, then here we see that test2 might have N*M rows

    however, if this is the same test2 table as above, we already know that there are only N rows in test2, and of course, by deduction, every combination of abc and xyz must be unique, so this FK is redundant

    holler if you didn't understand any of this

    Last edited by r937; 08-05-10 at 23:48.
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Threads merged.
    One question, one thread please.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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