Is there any difference between these two foreign key query?

vs.

Is there any difference between these two foreign key query?

vs.

yes, sir, absolutely there is a big difference

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

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.

sorry, I do not have that in my actual database. I am just asking because I saw other's query.

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?

okay, i understand

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

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?

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

Threads merged.
One question, one thread please.

pootle flump ur codings are working excelent.