SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint. Can't add foreign key to table with preexisting data

Question

I have searched all over with no luck and keep getting this error. I have checked both tables and all data types match and an index has been created. Any help would be appreciated! This is the statement I am using:

DB::statement(
        "ALTER TABLE `customers`
                ADD CONSTRAINT `customers_lang_code_foreign`
                  FOREIGN KEY (`lang_code`)
                  REFERENCES `languages` (`iso_code`)
                  ON DELETE RESTRICT
                  ON UPDATE CASCADE;"
    );

Edit: these are all the error I get:

  Illuminate\Database\QueryException  : SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint (SQL: ALTER TABLE `customers`
                ADD CONSTRAINT `customers_lang_code_foreign`
                  FOREIGN KEY (`lang_code`)
                  REFERENCES `languages` (`iso_code`)
                  ON DELETE RESTRICT
                  ON UPDATE CASCADE;)



 at /home/vagrant/code/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664
    660|         // If an exception occurs when attempting to run a query, we'll format the error
    661|         // message to include the bindings with SQL, which will make this exception a
    662|         // lot more helpful to the developer instead of just the database's errors.
    663|         catch (Exception $e) {
  > 664|             throw new QueryException(
    665|                 $query, $this->prepareBindings($bindings), $e
    666|             );
    667|         }
    668| 

  Exception trace:

  1   Doctrine\DBAL\Driver\PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /home/vagrant/code/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119

  2   PDOException::("SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint")
      /home/vagrant/code/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

  Please use the argument -v to see more details.

Refer to these posts https://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint https://dzone.com/articles/dealing-with-mysql-error-code-1215-cannot-add-foreign-key-constraint — subbul, Aug 12 '20 at 15:29

score 0 · Answer 1 · answered Aug 12 '20 at 14:16

0

There could be several reasons for that, I'd guess column types do not match. Check this article for more ideas.

answered Aug 12 '20 at 14:16

Robertas Murza

46
1
3

Saw that article. As far none of those solve my issue. As for the column types. I check multiple times and in both tables it is CHAR(2) – Ricards Aug 12 '20 at 14:18
It would help if you could show us table structure. Please post output of `show create table languages` – Robertas Murza Aug 12 '20 at 14:22
CREATE TABLE languages ( iso_code char(2) COLLATE utf8mb4_bin NOT NULL, title varchar(45) COLLATE utf8mb4_bin NOT NULL, created_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (iso_code) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin – Ricards Aug 13 '20 at 06:30
I've created `languages` table from this and a simple `customers` table: `create table customers( id int not null auto_increment, lang_code char(2) COLLATE utf8mb4_bin, PRIMARY KEY (id)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin` Inserted some data to both tables. There were no problems creating FK. Please double check type of column `customers.lang_code`. Both charset and collation must match. Also, check if all `lang_code`s exists in language table. – Robertas Murza Aug 13 '20 at 12:30

score 0 · Answer 2 · answered Aug 12 '20 at 14:17

You cannot add FK constraint on tables with existing data in the column where the constraint is going to be added. You'll have to drop your data before adding constraint and then re-hydrate tables.

You can also disable foreign key check:

SET FOREIGN_KEY_CHECKS = 0;
            //ALTER TABLE ...
SET FOREIGN_KEY_CHECKS = 1;

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint. Can't add foreign key to table with preexisting data

2 Answers2