Error 3780 hy000 referencing column

This error occurs in MySQL when the column you are attempting to add a foreign key constraint on does not have a matching data type with the column you are linking to in the parent table.

This error occurs in MySQL when the column you are attempting to add a foreign key constraint on does not have a matching data type with the column you are linking to in the parent table.

Example

Consider the following table pupils that contains pupil names:

CREATE TABLE pupils (

   id INT UNSIGNED AUTO_INCREMENT,

   name VARCHAR(30),

   PRIMARY KEY (id)

);

INSERT INTO pupils (name) VALUES ('axel'), ('bob'), ('cathy');

SELECT * FROM pupils;

+----+-------+

| id | name |

+----+-------+

| 1 | axel |

| 2 | bob |

| 3 | cathy |

+----+-------+

To create a new table product with a foreign key constraint on bought_by that refers to id from the pupil table:

CREATE TABLE product (

id INT UNSIGNED AUTO_INCREMENT,

name VARCHAR(30),

   bought_by INT,

PRIMARY KEY (id),

   FOREIGN KEY (bought_by) REFERENCES pupil (id)

);

ERROR 3780 (HY000): Referencing column 'bought_by' and referenced column 'id' in foreign key constraint 'product_ibfk_1' are incompatible.

Even though both columns are type INT, as the id column in pupils is UNSIGNED (i.e. cannot take negative values) and the bought_by column is SIGNED (i.e. can take negative values) these columns are considered incompatible. Updating one of the column types to match the other will allow you to create the table with the foreign key constraint.

Now specifying bought_by as INT UNSIGNED:

CREATE TABLE product (

id INT UNSIGNED AUTO_INCREMENT,

name VARCHAR(30),

   bought_by INT UNSIGNED,

PRIMARY KEY (id),

   FOREIGN KEY (bought_by) REFERENCES pupil (id)

);

Query OK, 0 rows affected (0.01 sec)

We are able to create the table successfully with the foreign key.

Источник

Содержание

  1. 5 Reasons MySQL Foreign Key constraints fail to create
  2. Finding out why Foreign key creation fail
  3. Reason #1 – Missing unique index on the referenced table
  4. Reason #2 – Different data types on the columns
  5. Reason #3 – Different collation/charset type on the table
  6. Reason #4 – Different collation types on the columns
  7. Reason #5 -Inconsistent data
  8. Error code 3780 mysql
  9. ОШИБКА 3780: ссылочный столбец и ссылочный столбец в ограничении внешнего ключа несовместимы
  10. 3 ответа
  11. Проблема миграции Laravel Общая ошибка: 3780
  12. 2 ответа
  13. Русские Блоги
  14. Ошибка настройки внешнего ключа MYSQL: 3780, 1452
  15. MYSQL
  16. Подсказка, когда MYSQL создает внешний ключ: ошибка 3780
  17. Подсказка, когда MYSQL создает внешние ключи: ошибка 1452
  18. Интеллектуальная рекомендация
  19. Реализация оценки приложения iOS
  20. JS функциональное программирование (е)
  21. PWN_JarvisOJ_Level1
  22. Установка и развертывание Kubernetes
  23. На стороне многопроцессорного сервера — (2) *

5 Reasons MySQL Foreign Key constraints fail to create

Finding out why Foreign key creation fail

When MySQL is unable to create a Foreign Key, it throws out this generic error message:

ERROR 1215 (HY000): Cannot add foreign key constraint

– The most useful error message ever.

Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.

That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:

This output could give you some clue about the actual reason why MySQL could not create your Foreign Key

Reason #1 – Missing unique index on the referenced table

This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:

In the all below examples, we’ll use a simple ‘Employee to Department” relationship:

As you may have noticed, we have not created the table with PRIMARY KEY or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id column and departments.id column:

Let’s look at the detailed error:

This is because we don’t have any unique index on the referenced table i.e. departments . We have two ways of fixing this:

Option 1: Primary Keys

Let’s fix this by adding a primary key departments.id

Option 2: Unique Index

Reason #2 – Different data types on the columns

MySQL requires the columns involved in the foreign key to be of the same data types.

You may have noticed that employees.department_id is int while departments.id is char(20) . Let’s try to create a foreign key now:

Let’s fix the type of departments.id and try to create the foreign key again:

Reason #3 – Different collation/charset type on the table

This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):

Let’s start from scratch to explain this scenario:

You may notice that we are using a different character set ( utf8 and latin 1` for both these tables. Let’s try to create the foreign key:

It failed because of different character sets. Let’s fix that.

If you have many tables with a different collation/character set, use this script to generate a list of commands to fix all tables at once:

Reason #4 – Different collation types on the columns

This is a rare reason, similar to reason #3 above but at a column level.

Let’s try to reproduce this from scratch:

We are using a different character set for employees.department_id and departments.id ( utf8 and latin1 ). Let’s check if the Foreign Key can be created:

Nope, as expected. Let’s fix that by changing the character set of departments.id to match with employees.department_id :

Reason #5 -Inconsistent data

This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.

Let’s setup some inconsistent data to reproduce this problem:

Let’s insert a department_id in employees table that will not exist in departments.id :

Let’s create a foreign key now and see if it works:

This error message is atleast more useful. We can fix this in two ways. Either by adding the missing department in departments table or by deleting all the employees with the missing department. We’ll do the first option now:

Let’s try to create the Foreign Key again:

It worked this time.

So we have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.

If you are using MySQL 8.x, the error message will be a little different:

Источник

Error code 3780 mysql

I created a many to many relationship between User and Language: n

public function languages()n<n return $this->belongsToMany(Language::class);n>n n

public function users()n<n return $this->belongsToMany(User::class);n>n n

Schema::create(‘languages’, function (Blueprint $table) <n $table->id();n $table->text(‘name’);n $table->text(‘sigla’);n $table->timestamps();n >);n n

Schema::create(‘users’, function (Blueprint $table) <n $table->id();n $table->string(‘name’);n $table->string(’email’)->unique();n $table->timestamp(’email_verified_at’)->nullable();n $table->string(‘password’);n $table->text(‘img’)->nullable();n $table->rememberToken();n $table->string(‘api_token’, 64)->unique()->default(Str::random(50));n $table->timestamps();n >);n n

Schema::create(‘user_language’, function (Blueprint $table) <n $table->id();n $table->unsignedInteger(‘lang_id’);n $table->unsignedInteger(‘user_id’);n $table->timestamps();nn $table->unique(‘lang_id’,’user_id’);nn $table->foreign(‘lang_id’)->references(‘id’)->on(‘languages’)->onDelete(‘cascade’);n $table->foreign(‘user_id’)->references(‘id’)->on(‘users’)->onDelete(‘cascade’);nnn >);n n

unfortunately the latter gives me the following error: n

since the default for ->id() is a big integer in the newer Laravel versions. n

Make sure you add the table name in your relationships, since that won’t work. Your table should be named language_user to work out of the box. n»,»bodyInMarkdown»:»@gianmarx change them to this:nn«`n$table->unsignedBigInteger(‘lang_id’);n$table->unsignedBigInteger(‘user_id’);n«`nnsince the default for `->id()` is a big integer in the newer Laravel versions.nnMake sure you add the table name in your relationships, since that won’t work. Your table should be named `language_user` to work out of the box.»,»replies»:[<«id»:675132,»conversation_id»:149848,»body»:»

>>> $a->languagen=> nulln>>> n n

I created 5 elements related to this user with id 2 unfortunately when I go with tinker it gives me this problem it tells me null n

so then just add the table name in here: n

I changed the name as you said unfortunately I always have the same error: n

Schema::create(‘language_user’, function (Blueprint $table) <n $table->id();n $table->unsignedBigInteger(‘lang_id’);n $table->unsignedBigInteger(‘user_id’);nn $table->timestamps();nn $table->unique(‘lang_id’,’user_id’);nn $table->foreign(‘lang_id’)->references(‘id’)->on(‘languages’)->onDelete(‘cascade’);n $table->foreign(‘user_id’)->references(‘id’)->on(‘users’)->onDelete(‘cascade’);n >);n n

And it will always be null since you don’t have any field language in your User model. n

It should be $user->languages instead, look at the name of the function that you added in the User model. n

And I helped you with the main issue, sorry but I don’t have time to teach you basic in programming. There are more than 1800 videos here on Laracasts, start watching and learning. n

i change this : n

Schema::create(‘language_user’, function (Blueprint $table) <n $table->id();n $table->unsignedBigInteger(‘lang_id’);n $table->unsignedBigInteger(‘user_id’);nn $table->timestamps();n $table->foreign(‘lang_id’)->on(‘languages’)->onDelete(‘cascade’);n $table->foreign(‘user_id’)->on(‘users’)->onDelete(‘cascade’);n >);nnnn SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘) on delete cascade’ at line 1 (SQL: alter table `language_user` add constraint `language_user_lang_id_foreign` foreign key (`lang_id`) references `languages` () on delete cascade)n n

rnWe don’t learn tools for the sake of learning tools. Instead, we learn them because they help us accomplish a particular goal. With that in mind, in this series, we’ll use the common desire for a blog — with categories, tags, comments, email notifications, and more — as our goal. Laravel will be the tool that helps us get there. Each lesson, geared toward newcomers to Laravel, will provide instructions and techniques that will get you to the finish line. rn rn

Источник

ОШИБКА 3780: ссылочный столбец и ссылочный столбец в ограничении внешнего ключа несовместимы

Я создал схему в MySQL 8.0 с именем hr. Я создаю две таблицы: одна locations, а другая departments, у которой есть внешний ключ, ссылающийся на таблицу locations.

При обработке появляется такая ошибка:

Код ошибки: 3780. Ссылочный столбец «location_id» и ссылочный столбец «location_id» в ограничении внешнего ключа «departments_ibfk_1» несовместимы.

Тип данных для location_id одинаков в обеих таблицах. Я не могу найти ошибку.

3 ответа

У меня была эта проблема, когда одно из свойств было помечено как Unsigned, а другое было помечено как Signed. Изменение обоих свойств для соответствия состоянию Unsigned/Signed устранило ошибку.

Пожалуйста, отметьте «character set» и «collation» в обоих связанных полях, если тип данных — «string/char».

Такой проблемы нет («ограничение внешнего ключа ‘xxx’ несовместимо») для целочисленного типа данных (по крайней мере, я не встречал таких несовместимостей).

Иногда созданная таблица имеет другой «набор/сортировку» от сторонних таблиц (чужая таблица, созданная другими, восстановленная из старого дампа и т.д.).

Если создать новую таблицу (устав/сопоставление должно быть таким же, как внешняя таблица):

Если у вас уже есть обе таблицы и вы хотите изменить столбец:

Вы пытались определить location_id как char(4)? В обеих таблицах, разумеется.

Источник

Проблема миграции Laravel Общая ошибка: 3780

У меня есть разработчик, который в значительной степени отказался от проекта. У меня есть код с сервера, и я пытаюсь заставить его работать в моей локальной среде. Я пытаюсь заставить его мигрировать, но у меня продолжает возникать проблема с ссылками, я посмотрел на сообщения и попробовал все, начиная с добавления disableForeignKeyConstraints (); в дамп-автозагрузку композитора и т. д. Но все равно продолжаю получать

SQLSTATE [HY000]: общая ошибка: 3780 Ссылочный столбец ‘user_id’ и ссылочный столбец ‘id’ в ограничении внешнего ключа ‘role_user_user_id_foreign’ несовместимы. (SQL: изменить таблицу role_user добавить ограничение role_user_user_id_foreign внешний ключ ( user_id ) ссылается users ( id ) на каскад удаления)

SQLSTATE [HY000]: общая ошибка: 3780 Ссылочный столбец ‘user_id’ и ссылочный столбец ‘id’ в ограничении внешнего ключа ‘role_user_user_id_foreign’ несовместимы.

Я увидел, что это говорит о несовместимости, поэтому я попытался добавить unsigned (); для создания пользователя, но до сих пор нет игральных костей.

И пользователь создает миграцию

2 ответа

Похоже, вашей таблице role_user нужно сначала создать свою пользовательскую таблицу. Предположим, что ваши файлы миграции выглядят так:

1) create_users_table_migration.php 2) create_roles_table_migration.php

Они будут храниться в laravelProject / database / migrations

Попробуйте запустить только миграцию пользователя с помощью следующей команды:

А затем путем миграции таблицы user_roles:

Эти элементы не работали, и, так как у меня была копия базы данных, я сделал обратную миграцию информация найдена здесь для генератора миграции

Даже тогда я столкнулся с проблемами в классе, я просто удалил файлы миграции из папки поставщика, которую использовал разработчик, и смог запустить новую миграцию. Спасибо всем!

Источник

Русские Блоги

Ошибка настройки внешнего ключа MYSQL: 3780, 1452

MYSQL

Подсказка, когда MYSQL создает внешний ключ: ошибка 3780

причина: Набор символов и сопоставление не соответствуют. (При этом обратите внимание на тип данных, длину)

Разница между utf8mb4 и utf8
Разница между utf8mb4 и utf8 : https://blog.csdn.net/qq_17555933/article/details/101445526

MySQL добавил кодировку символов utf8mb4 после 5.5.3, mb4 — это наибольшее количество байтов 4. Проще говоря, utf8mb4 является надмножеством utf8 и полностью совместим с utf8 и может хранить больше символов с четырьмя байтами.

Utf8mb4 в Mysql является дополнением к исходному utf8, который может хранить только 3 байта символов и представляет собой настоящую кодировку UTF-8.

Подсказка, когда MYSQL создает внешние ключи: ошибка 1452

причина: Некоторые данные (первичный ключ) существуют в дочерней таблице, но основная таблица не существует.
найти：

Продолжать сообщать об ошибках:

Проверка не проблема, некоторые идентификаторы в подтаблице находятся в основной таблице. Один из идентификаторов сообщил об ошибке!

Перезагрузите компьютер 、 Перезагрузите MySQL 、 Удалите эти данные в основной таблице и добавьте их снова Все бесполезно

Удалите эти данные в таблице отношений и добавьте их снова.

Все данные были проверены, и некоторые дочерние и родительские таблицы обязательно существуют, но сообщение об ошибке все равно отображается! ! !

Я застрял на день, поэтому прошу решение! ! !

Интеллектуальная рекомендация

Реализация оценки приложения iOS

Есть два способа получить оценку приложения: перейти в App Store для оценки и оценка в приложении. 1. Перейдите в App Store, чтобы оценить ps: appid можно запросить в iTunes Connect 2. Встроенная оцен.

JS функциональное программирование (е)

Давайте рассмотрим простой пример, чтобы проиллюстрировать, как используется Reduce. Первый параметр Reduce — это то, что мы принимаем массив arrayOfNums, а второй параметр — функцию. Эта функция прин.

PWN_JarvisOJ_Level1

Nc первый Затем мы смотрим на декомпиляцию ida Перед «Hello, World! N» есть уязвимая_функция, проверьте эту функцию после ввода Видно, что только что появившийся странный адрес является пе.

Установка и развертывание Kubernetes

На самом деле, я опубликовал статью в этом разделе давным -давно, но она не достаточно подробно, и уровень не является ясным. Когда я развернулся сегодня, я увидел его достаточно (хотя это было успешн.

На стороне многопроцессорного сервера — (2) *

Обработка сигнала Родительский процесс часто очень занят, поэтому вы не можете просто вызвать функцию waitpid, чтобы дождаться завершения дочернего процесса. Затем обсудите решение. Обратитесь .

Источник

Источник

Issue

I’m getting the following order trying to use a table’s primary key as another table’s primary key:

ERROR 3780 (HY000): Referencing column ‘optimization_id’ and referenced column ‘optimization_id’ in foreign key constraint ‘optimization.main – optimization.status’ are incompatible.

I’m using the primary key of OPTIMIZATION.main as the primary key of OPTIMIZATION.status as well since it’s a one-to-one relationship and I thought this would be easier/simpler than just adding an id in every table as the primary key. These are the only tables where I’m doing this, the other ones I’m not using the foreign key as the primary key as well. However, it seemed easier/simpler to do this for the few important tables I’m using to organize the data together.

My questions therefore are:

  1. Why am I getting error 3780 using the provided code?
  2. If this isn’t best practice, what would be another way of doing this?
  3. Is it even possible to use a different table’s primary key (like
    main) as the primary key for another table (like status)?

In this DBfiddle is the code I’m using, generated by SqlDBM, to create these two tables.

Here is the visualization of the two tables (one visualized relationship isn’t applicable in this case) through the SqlDBM software.

Visualization of tables

Solution

in main table the id is an int unsigned in the status table it is only int which are incompatible. change both to unsigned int or only int

CREATE TABLE `OPTIMIZATION`.`main`
(
 `optimization_id`       int unsigned NOT NULL ,
 `optimization_name`     varchar(250) NOT NULL ,
 `optimization_category` varchar(200) NULL ,

PRIMARY KEY (`optimization_id`)
);

CREATE TABLE `OPTIMIZATION`.`status`
(
 `current_status`  varchar(25) NULL ,
 `discovery_date`  datetime NULL ,
 `processed_date`  datetime NULL ,
 `processed_by`    varchar(100) NULL ,
 `optimization_id` int unsigned NOT NULL ,

PRIMARY KEY (`optimization_id`),
KEY `fkIdx_409` (`optimization_id`),
CONSTRAINT `optimization.main - optimization.status` FOREIGN KEY `fkIdx_409` (`optimization_id`) REFERENCES `OPTIMIZATION`.`main` (`optimization_id`)
);

Answered By – Jens

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0

Источник

Finding out why Foreign key creation fail

When MySQL is unable to create a Foreign Key, it throws out this generic error message:

ERROR 1215 (HY000): Cannot add foreign key constraint

– The most useful error message ever.

Fortunately, MySQL has this useful command that can give the actual reason about why it could not create the Foreign Key.

mysql> SHOW ENGINE INNODB STATUS;

That will print out lots of output but the part we are interested in is under the heading ‘LATEST FOREIGN KEY ERROR’:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-29 13:40:56 0x7f3cb452e700 Error in foreign key constraint of table test_database/my_table:
there is no index in referenced table which would contain
the columns as the first columns, or the data types in the
referenced table do not match the ones in table. Constraint:
,
CONSTRAINT idx_name FOREIGN KEY (employee_id) REFERENCES employees (id)
The index in the foreign key in table is idx_name
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This output could give you some clue about the actual reason why MySQL could not create your Foreign Key

Reason #1 – Missing unique index on the referenced table

This is probably the most common reason why MySQL won’t create your Foreign Key constraint. Let’s look at an example with a new database and new tables:

In the all below examples, we’ll use a simple ‘Employee to Department” relationship:

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)
mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20)
    -> );
Query OK, 0 rows affected (0.07 sec)

As you may have noticed, we have not created the table with PRIMARY KEY or unique indexes. Now let’s try to create Foreign Key constraint between employees.department_id column and departments.id column:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s look at the detailed error:

mysql> SHOW ENGINE INNODB STATUS;
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2020-08-31 09:25:13 0x7fddc805f700 Error in foreign key constraint of table foreign_key_1/#sql-5ed_49b:
FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.

This is because we don’t have any unique index on the referenced table i.e. departments. We have two ways of fixing this:

Option 1: Primary Keys

Let’s fix this by adding a primary key departments.id

mysql> ALTER TABLE departments ADD PRIMARY KEY (id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Option 2: Unique Index

mysql> CREATE UNIQUE INDEX idx_department_id ON departments(id);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

Reason #2 – Different data types on the columns

MySQL requires the columns involved in the foreign key to be of the same data types.

mysql> CREATE DATABASE foreign_key_1;
Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;
Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id char(20),
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

You may have noticed that employees.department_id is int while departments.id is char(20). Let’s try to create a foreign key now:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Let’s fix the type of departments.id and try to create the foreign key again:

mysql> ALTER TABLE departments MODIFY id INT;
Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #3 – Different collation/charset type on the table

This is a surprising reason and hard to find out. Let’s create two tables with different collation (or also called charset):

Let’s start from scratch to explain this scenario:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=utf8;
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> ) ENGINE=InnoDB CHARACTER SET=latin1;
Query OK, 0 rows affected (0.08 sec)

You may notice that we are using a different character set (utf8 and latin1` for both these tables. Let’s try to create the foreign key:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

It failed because of different character sets. Let’s fix that.

mysql> SET foreign_key_checks = 0; ALTER TABLE departments CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.18 sec)
Records: 0  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

If you have many tables with a different collation/character set, use this script to generate a list of commands to fix all tables at once:

mysql --database=your_database -B -N -e "SHOW TABLES" | awk '{print "SET foreign_key_checks = 0; ALTER TABLE", $1, "CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; SET foreign_key_checks = 1; "}'

Reason #4 – Different collation types on the columns

This is a rare reason, similar to reason #3 above but at a column level.

Let’s try to reproduce this from scratch:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id char(26) CHARACTER SET utf8,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> CREATE TABLE departments(
    ->     id char(26) CHARACTER SET latin1,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

We are using a different character set for employees.department_id and departments.id (utf8 and latin1). Let’s check if the Foreign Key can be created:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
ERROR 1215 (HY000): Cannot add foreign key constraint

Nope, as expected. Let’s fix that by changing the character set of departments.id to match with employees.department_id:

mysql> ALTER TABLE departments MODIFY id CHAR(26) CHARACTER SET utf8;
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 0 rows affected (0.20 sec)
Records: 0  Duplicates: 0  Warnings: 0

It works now!

Reason #5 -Inconsistent data

This would be the most obvious reason. A foreign key is to ensure that your data remains consistent between the parent and the child table. So when you are creating the foreign key, the existing data is expected to be already consistent.

Let’s setup some inconsistent data to reproduce this problem:

mysql> CREATE DATABASE foreign_key_1;                                                                                        Query OK, 1 row affected (0.00 sec)

mysql> USE foreign_key_1;                                                                                                    Database changed

mysql> CREATE TABLE employees(
    ->     id int,
    ->     name varchar(20),
    ->     department_id int,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE departments(
    ->     id int,
    ->     name varchar(20),
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.08 sec)

Let’s insert a department_id in employees table that will not exist in departments.id:

mysql> INSERT INTO employees VALUES (1, 'Amber', 145);
Query OK, 1 row affected (0.01 sec)

Let’s create a foreign key now and see if it works:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`foreign_key_1`.`#sql-5ed_49b`, CONSTRAINT `fk_department_id` FOREIGN KEY (`department_id`) REFERENCES `departments` (`id`))

This error message is atleast more useful. We can fix this in two ways. Either by adding the missing department in departments table or by deleting all the employees with the missing department. We’ll do the first option now:

mysql> INSERT INTO departments VALUES (145, 'HR');
Query OK, 1 row affected (0.00 sec)

Let’s try to create the Foreign Key again:

mysql> ALTER TABLE employees ADD CONSTRAINT fk_department_id FOREIGN KEY idx_employees_department_id (department_id) REFERENCES departments(id);
Query OK, 1 row affected (0.24 sec)
Records: 1  Duplicates: 0  Warnings: 0

It worked this time.

So we have seen 5 different ways a Foreign Key creation can fail and possible solutions of how we can fix them. If you have encountered a reason not listed above, add them in the comments.

If you are using MySQL 8.x, the error message will be a little different:

SQLSTATE[HY000]: General error: 3780 Referencing column 'column' and referenced column 'id' in foreign key constraint 'idx_column_id' are incompatible.
Источник

Hi @alfonsobries

php artisan migrate —seed causes this error:


   IlluminateDatabaseQueryException  : SQLSTATE[HY000]: General error: 3780 Referencing column 'column_id' and referenced column 'id' in foreign key constraint 'column_layout_key_column_id_foreign' are incompatible. (SQL: alter table `column_layout_key` add constraint `column_layout_key_column_id_foreign` foreign key (`column_id`) references `columns` (`id`) on delete cascade)

  at /Users/kunalpunjabi/Code/EXPLORATIONS/alfonsobries_laravel_nuxt_tailwind/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   DoctrineDBALDriverPDOException::("SQLSTATE[HY000]: General error: 3780 Referencing column 'column_id' and referenced column 'id' in foreign key constraint 'column_layout_key_column_id_foreign' are incompatible.")
      /Users/kunalpunjabi/Code/EXPLORATIONS/alfonsobries_laravel_nuxt_tailwind/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:119

  2   PDOException::("SQLSTATE[HY000]: General error: 3780 Referencing column 'column_id' and referenced column 'id' in foreign key constraint 'column_layout_key_column_id_foreign' are incompatible.")
      /Users/kunalpunjabi/Code/EXPLORATIONS/alfonsobries_laravel_nuxt_tailwind/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:117

Instead of submitting a PR, this is the (one-line) fix:
In 2019_06_17_230928_create_columns_table.php
change
$table->bigIncrements(‘id’);
to
$table->increments(‘id’);

Источник

Понравилась статья? Поделить с друзьями:
  • Error 3734 failed to add the foreign key constraint
  • Error 372 failed to load control sstab from tabctl32 ocx
  • Error 3706 teradata
  • Error 37 xxcactaurxcorexx текст
  • Error 37 xxcactaurxcorexx скачать