Mysql – Does the order I declare foreign key constraints in matter

deadlockforeign keylockingMySQL

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?

Best Answer

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.

Related Solutions

PostgreSQL – insert/update violates foreign key constraints

There are a few problems with your tables. I'll try to address the foreign keys first, since you question asked about them :)

But before that, we should realize that the two sets of tables (the first three you created and the second set, which you created after dropping the first set) are the same. Of course, the definition of Table3 in your second attempt has syntax and logical errors, but the basic idea is:

CREATE TABLE table3 (   
  "ID" bigint NOT NULL DEFAULT '0',   
  "DataID" bigint DEFAULT NULL,   
  "Address" numeric(20) DEFAULT NULL,   
  "Data" bigint DEFAULT NULL,
   PRIMARY KEY ("ID"),   
   FOREIGN KEY ("DataID") REFERENCES Table1("DataID") on delete cascade on update cascade,   
   FOREIGN KEY ("Address") REFERENCES Table2("Address") on delete cascade on update cascade
);

This definition tell PostgreSQL roughly the following: "Create a table with four columns, one will be the primary key (PK), the others can be NULL. If a new row is inserted, check DataID and Address: if they contain a non-NULL value (say 27856), then check Table1 for DataID˙and Table2 for Address. If there is no such value in those tables, then return an error." This last point which you've seen first:

ERROR: insert or update on table "Table3" violates foreign key constraint 
    "Table3_DataID_fkey" DETAIL: Key (DataID)=(27856) is not present in table "Table1".

So simple: if there is no row in Table1 where DataID = 27856, then you can't insert that row into Table3.

If you need that row, you should first insert a row into Table1 with DataID = 27856, and only then try to insert into Table3. If this seems to you not what you want, please describe in a few sentences what you want to achieve, and we can help with a good design.

And now about the other problems.

You define your PKs as

CREATE all_your_tables (
    first_column NOT NULL DEFAULT '0',   
    [...]
    PRIMARY KEY ("ID"),

A primary key means that all the items in it are different from each other, that is, the values are UNIQUE. If you give a static DEFAULT (like '0') to a UNIQUE column, you will experience bad surprises all the time. This is what you got in your third error message.

Furthermore, '0' means a text string, but not a number (bigint or numeric in your case). Use simply 0 instead (or don't use it at all, as I written above).

And a last point (I may be wrong here): in Table2, your Address field is set to numeric(20). At the same time, it is the PK of the table. The column name and the data type suggests that this address can change in the future. If this is true, than it is a very bad choice for a PK. Think about the following scenario: you have an address '1234567890454', which has a child in Table3 like

ID        DataID           Address             Data
123       3216547          1234567890454       654897564134569

Now that address happens to change to something other. How do you make your child row in Table3 follow its parent to the new address? (There are solutions for this, but can cause much confusion.) If this is your case, add an ID column to your table, which will not contain any information from the real world, it will simply serve as an identification value (that is, ID) for an address.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;

Best Answer

Related Solutions

PostgreSQL – insert/update violates foreign key constraints

Mysql – Finding rows for a specified date range

Related Question