-1

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)
);
  • Please tag with version of MySQL you are using – bbaird Jan 14 at 15:05
  • Apologies, I have added it – George Shilton Jan 14 at 15:11
  • No worries. Are there other statements in the batch? Your statement seems to parse okay here dbfiddle.uk/… – bbaird Jan 14 at 15:39
  • The error is returned on the CREATE statement of this table, I dropped all tables in the DB, only 2 other CREATE tables prior to this one – George Shilton Jan 14 at 16:09
  • This is the full table creation list which also appears to not return issues dbfiddle.uk/… – George Shilton Jan 14 at 16:25
0

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

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.