SQL Antipatterns, Volume 1

Using Mismatched Data Types

You must define the foreign key columns in the Child table using the same data types as the respective columns they reference in the Parent table. If the data types don’t match, then you get an error.

Foreign-Key-Checklist/data-type-error.sql

CREATE TABLE Parent (
  parent_id INT PRIMARY KEY
);

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

ERROR 3780 (HY000): Referencing column ’parent_id’ and referenced column ’parent_id’ in foreign key constraint ’child_ibfk_1’ are incompatible.

The difference between a signed and unsigned integer is enough to make the columns incompatible.

Foreign-Key-Checklist/data-type-int-error.sql ...