SQL Antipatterns, Volume 1

Referencing Tables Before They Have Been Created

If the foreign key references a table before you have created that table, then you get an error.

Foreign-Key-Checklist/table-order-error.sql

CREATE TABLE Child (
  child_id INT PRIMARY KEY,
  parent_id INT NOT NULL,
  FOREIGN KEY (parent_id) REFERENCES Parent(parent_id)
);

CREATE TABLE Parent (
  parent_id INT PRIMARY KEY
);

The following error is returned as the first CREATE TABLE statement fails, before the second statement is run.

ERROR 1824 (HY000): Failed to open the referenced table ’Parent’

The order in which you create the tables is important. You must create the Parent table before you define a foreign key to reference it.

Foreign-Key-Checklist/table-order-correct.sql ...