Error 1005 errno 150 “Foreign key constraint is incorrectly formed” where no foreign key declared

Question

I have an issue on a basic CREATE TABLE statement Error 1005 errno 150 "Foreign key constraint is incorrectly formed" returned where no Foreign key is declared -

CREATE TABLE Person
(
  personID   VARCHAR(9),
  name       VARCHAR(100),
  birthYear  INTEGER(4),
  deathYear  INTEGER(4),
  CONSTRAINT PersonPK PRIMARY KEY (personID)
);

Answer 1

You can make PRIMARY KEYs 3 different ways

METHOD #1

CREATE TABLE Person1
(
  personID   VARCHAR(9),
  name       VARCHAR(100),
  birthYear  INTEGER(4),
  deathYear  INTEGER(4),
  PRIMARY KEY (personID)
);

METHOD #2

CREATE TABLE Person2
(
  personID   VARCHAR(9) NOT NULL PRIMARY KEY,
  name       VARCHAR(100),
  birthYear  INTEGER(4),
  deathYear  INTEGER(4)
);

METHOD #3 (Your Way)

CREATE TABLE Person3
(
  personID   VARCHAR(9),
  name       VARCHAR(100),
  birthYear  INTEGER(4),
  deathYear  INTEGER(4),
  CONSTRAINT PersonPK PRIMARY KEY (personID)
);

Let's compare them

mysql> create database db1;
Query OK, 1 row affected (0.02 sec)

mysql> use db1;
Database changed
mysql>     CREATE TABLE Person1
    ->     (
    ->       personID   VARCHAR(9),
    ->       name       VARCHAR(100),
    ->       birthYear  INTEGER(4),
    ->       deathYear  INTEGER(4),
    ->       PRIMARY KEY (personID)
    ->     );
    CREATE TABLE Person2
    (
      personID   VARCHAR(9) NOT NULL PRIMARY KEY,
      name       VARCHAR(100),
      birthYear  INTEGER(4),
      deathYear  INTEGER(4)
    );
    CREATE TABLE Person3
    (
      personID   VARCHAR(9),
Query OK, 0 rows affected, 2 warnings (0.03 sec)

mysql>     CREATE TABLE Person2
    ->     (
    ->       personID   VARCHAR(9) NOT NULL PRIMARY KEY,
    ->       name       VARCHAR(100),
    ->       birthYear  INTEGER(4),
    ->       deathYear  INTEGER(4)
    ->     );
      name       VARCHAR(100),
      birthYear  INTEGER(4),
      deathYear  INTEGER(4),
      CONSTRAINT PersonPK PRIMARY KEY (personID)
    );
Query OK, 0 rows affected, 2 warnings (0.02 sec)

mysql>     CREATE TABLE Person3
    ->     (
    ->       personID   VARCHAR(9),
    ->       name       VARCHAR(100),
    ->       birthYear  INTEGER(4),
    ->       deathYear  INTEGER(4),
    ->       CONSTRAINT PersonPK PRIMARY KEY (personID)
    ->     );
Query OK, 0 rows affected, 2 warnings (0.01 sec)

mysql>

Table Schemas

mysql> show create table Person1\G

*************************** 1. row ***************************
       Table: Person1
Create Table: CREATE TABLE `Person1` (
  `personID` varchar(9) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `birthYear` int DEFAULT NULL,
  `deathYear` int DEFAULT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.02 sec)

mysql> show create table Person2\G
*************************** 1. row ***************************
       Table: Person2
Create Table: CREATE TABLE `Person2` (
  `personID` varchar(9) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `birthYear` int DEFAULT NULL,
  `deathYear` int DEFAULT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql> show create table Person3\G
*************************** 1. row ***************************
       Table: Person3
Create Table: CREATE TABLE `Person3` (
  `personID` varchar(9) NOT NULL,
  `name` varchar(100) DEFAULT NULL,
  `birthYear` int DEFAULT NULL,
  `deathYear` int DEFAULT NULL,
  PRIMARY KEY (`personID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)

mysql>

Your ways works just fine in MySQL 8.0.22