Same column present on two or more foreign key constraints 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
);


This SQL Fiddle contains sample data and queries along with the schema shown above.

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 or 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 R)?
I'm not entirely clear on what the standard says about this, but clearly, there is a conflict between:

  a) FOREIGN KEY (AID) REFERENCES A(AID) ON DELETE NO ACTION


and 

  b) FOREIGN KEY (AID, CID) REFERENCES A_COPY(AID, CID) ON DELETE CASCADE


The final result may differ if a) is evaluated before b) or the other way around. There is a similar question in standard-behaviour-for-mix-of-on-delete-cascade-and-on-delete-restrict-const