CODEWITHSUNDEEP
mysqlmysql-workbench
user3025955
user3025955

Reputation: 21

ERROR: Error 1215: Cannot add foreign key constraint when no foreign constraint being added

Here is my total output:

Executing SQL script in server

ERROR: Error 1215: Cannot add foreign key constraint

CREATE TABLE IF NOT EXISTS `pharmacy`.`EMPLOYEE` (
  `id` INT UNSIGNED NOT NULL,
  `first_name` VARCHAR(45) NOT NULL,
  `last_name` VARCHAR(45) NOT NULL,
  `sex` VARCHAR(1) NULL,
  `dob` DATE NULL,
  `start_date` DATE NOT NULL,
  `pharmacist` TINYINT(1) NULL,
  `manager` TINYINT(1) NULL,
  UNIQUE INDEX `id_UNIQUE` (`id` ASC),
  PRIMARY KEY (`id`))
ENGINE = InnoDB

As you can see, I'm not trying to add a foreign key constraint which is why I'm stumped by this error.

Upvotes: 2

Views: 61

Answers (1)

Brandon McKenzie
Brandon McKenzie

Reputation: 1685

I encountered a similar situation. The cause of my problem was that the table name was referenced in my information_schema.KEY_COLUMN_USAGE table despite having previously been deleted. Try looking for any references that share the name with your table. The following query revealed my problem:

SELECT * FROM KEY_COLUMN_USAGE WHERE TABLE_NAME LIKE '%EMPLOYEE%';

That query turned up foreign keys for a previously dropped table that shared a name with the one I was attempting to create within my schema (I am not sure why the information_schema still had references to it, possibly a mySQL bug?).

Since my database was being generated from a dump, I ended up dropping the schema and recreating, which implicitly deleted the offending row. If that's not an option and you have, or can get, root privileges, you may also be able to delete any offending rows, but I would be super cautious about it.

Upvotes: 1

Related Questions