A simple schema leading to my question is the following:
CREATE TABLE A ( AID INT AUTO_INCREMENT PRIMARY KEY ); CREATE TABLE A_COPY ( AID INT, CID INT, PRIMARY KEY (AID, CID), FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE CASCADE ); CREATE TABLE R ( AID INT, CID INT, EXTRANUM INT, PRIMARY KEY (AID, CID, EXTRANUM), FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE NO ACTION, FOREIGN KEY (AID, CID) REFERENCES A_COPY(AID, CID) ON DELETE CASCADE );
Here, as you can see, we build up on the
A table by extending it (say that
A_COPY is a "weak entity" in an Entity Relationship diagram) and then use it in some relation
R. The question is, that here (table
R) we use two different foreign key constraints where one column is the same in both constraints. That might, or might not be meaningful.
So, one thing is whether this would be meaningful. I believe it would, since I might (for some reason) want to opt for different foreign key referential actions when deleting from table
A_COPY respectively, as shown here.
The second question is how do these statements get implemented in MySQL with InnoDB tables. Generally, the question is: what is the chosen rule for foreign key constraints on the same table with two or more identical columns part of the respective foreign key constraints (e.g. here
AID is used identically in two foreign key constraints on the same table