2

Does it matter what order I add foreign key constraints to a table in, or what order I write them in a CREATE TABLE statement? MySQL clearly at least remembers the order; if I run the DDL statements below...

CREATE TABLE foo(id INT PRIMARY KEY);
CREATE TABLE bar(id INT PRIMARY KEY);
CREATE TABLE baz (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (bar_id) REFERENCES bar (id),
  foreign key (foo_id) REFERENCES foo (id)
);
CREATE TABLE qux (
  foo_id INT,
  bar_id INT,
  FOREIGN KEY (foo_id) REFERENCES foo (id),
  FOREIGN KEY (bar_id) REFERENCES bar (id)
);

... and then run SHOW CREATE TABLE on each of baz and qux...

mysql> SHOW CREATE TABLE baz\G
*************************** 1. row ***************************
       Table: baz
Create Table: CREATE TABLE `baz` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `bar_id` (`bar_id`),
  KEY `foo_id` (`foo_id`),
  CONSTRAINT `baz_ibfk_1` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`),
  CONSTRAINT `baz_ibfk_2` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE qux\G
*************************** 1. row ***************************
       Table: qux
Create Table: CREATE TABLE `qux` (
  `foo_id` int(11) DEFAULT NULL,
  `bar_id` int(11) DEFAULT NULL,
  KEY `foo_id` (`foo_id`),
  KEY `bar_id` (`bar_id`),
  CONSTRAINT `qux_ibfk_1` FOREIGN KEY (`foo_id`) REFERENCES `foo` (`id`),
  CONSTRAINT `qux_ibfk_2` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
1 row in set (0.00 sec)

... then we can see that MySQL remembered that the foreign key pointing to foo comes first in baz, but second in qux.

It's possible to imagine this order making a difference; for instance, if MySQL, upon insert into a table with foreign keys, takes out locks on referenced tables in the order that the foreign keys are declared, then simultaneous inserts into baz and qux in my example above would have the potential to deadlock, and so it would be a best practice to ensure that foreign keys were always declared in the same order on all tables (e.g. by alphabetical order of the referenced table name). Do any such considerations exist in reality, or does the order of foreign key constraints have no effect on anything?

  • What happens if you name the constraint in a way that they are alphabetically in reverse order? SHOW CREATE still produces the constraint in the creation order or not? I'm guessing here but because of atomicity I expect the order of creation of constraints to be irrelevant for anything. – Paolo Jan 13 '17 at 7:23
-2

The order of FK Definition does not matter. When a transaction on table with FKs completes, it will release the lock on referenced tables simultaneously.

  • 2
    -1; this might be true for all I know, but this answer isn't useful without evidence - either empirical or in the form of docs quotes or analysis of MySQL's source code. What's your basis for this claim? Why should anyone believe you? – Mark Amery Jan 11 '17 at 13:04
  • 2
    Also - this answer talks about simultaneous release of keys, but says nothing about the acquisition of keys, which is where a deadlock would occur. So even if the information here is accurate, it's not clear to me how it's relevant. – Mark Amery Jan 11 '17 at 13:05

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.