1

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

  • When in doubt, use application code instead of FOREIGN KEYs, TRIGGERs, etc. Meanwhile, did you intend to have extranum as part of the PRIMARY KEY? – Rick James Jun 1 at 15:25
  • @RickJames Yes, it is just something extra, probably another foreign key in another table, my intent was not to make it fully useful, only study the technical effect. And yes, I am wondering if there is any related documented behaviour. – Jason Jun 1 at 15:38
  • Why does R have a FK to both A and A_COPY when the FK to A_COPY will ensure that it also meets the requirements of the FK to A – Joe W Jun 4 at 19:30
  • @JoeW because of the ability, if given by the RDBMS, to act on a different referential action to some of these two. That is exactly the point of the whole question, what the technical specification does. – Jason Jun 5 at 7:50
  • I think there is a difference in having the same column in multiple FK's and having a FK to one table and a different FK that duplicates the FK that the other table also has. For example having a FK on columns A,B and one on A,C because they go to different tables instead of one on A,B and A alone. – Joe W Jun 5 at 12:23
1
+50

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

0

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.

  • 1
    First of all, ON DELETE NO ACTION does not "do nothing", it acts like RESTRICT. Secondly, that's the point of this question: finding what it does technically, which probably here means "how" it does. – Jason Jun 5 at 8:16
  • What does "tied together" mean & how does it mean anything other than that the other bullets apply? – philipxy Jun 9 at 1:01
  • @philipxy - Nothing, other that there is likely to be a JOIN on that column. – Rick James Jun 10 at 4:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.