Same column present on two or more foreign key constraints

Question

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)?

score 1 · Answer 1 · answered Jun 04 '19 at 19:47

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

score 0 · Answer 2 · answered Jun 04 '19 at 19:12

A FOREIGN KEY does these things:

Tell humans how the schema designer expects tables to be tied together.
Do integrity checks when INSERTing -- to verify that the corresponding row exists.
Do CASCADE things when UPDATEing or DELETEing.
Provide an INDEX -- if one does not already exist -- to facilitate the lookup needed for the integrity or cascade.

Since you seem to have two conflicting CASCADE requirements, there may be a rule described deep in the bowels of the documentation. I would guess that the FK actions are performed in the order written. Hence ON DELETE NO ACTION would do nothing, then ON DELETE CASCADE would take over.

In my opinion, FKs are very simple minded; expecting more than trivial actions from them is risky. This forum is littered with Questions asking "can I write an FK to do blah". The answer is usually "no".

You are asking a corollary question "what happens in this weird case". My answer is "Don't trust MySQL, use app code to do whatever you want." (And you might embody that "business logic" in a Stored Routine.)

Beware of asking too much from Triggers, too.

Same column present on two or more foreign key constraints

2 Answers2