DROP DATABASE IF EXISTS ProviderPatients;
CREATE DATABASE ProviderPatients;
USE ProviderPatients;
CREATE TABLE IF NOT EXISTS Date_Dim
(
Date_ID integer not null,
Date_ date,
Full_Date_Des varchar(25) not null,
Day_Of_Week int(11) not null,
Calender_Year int(11) not null,
Weekday_Indicator int(11) not null,
PRIMARY KEY (Date_ID)
);
CREATE TABLE IF NOT EXISTS Insurer_DIM
(
Insurer_ID int(11) not null,
Insurer_Name varchar(25) not null,
Line_Of_Buissness varchar(25) not null,
PRIMARY KEY (Insurer_ID)
);
CREATE TABLE IF NOT EXISTS Member_DIM
(
Member_ID int(11) not null,
Member_Name varchar(25) not null,
Age int(11) not null,
Ethnicity varchar(25) not null,
Health_Condition varchar(25) not null,
PRIMARY KEY (Member_ID)
);
CREATE TABLE IF NOT EXISTS Geography_Dim
(
Geography_ID varchar(25) not null,
Country varchar(25) not null,
State varchar(10) not null,
State_Code int(11) not null,
County_Code int(11) not null,
PRIMARY KEY (Geography_ID)
);
CREATE TABLE Provider_Dim
(
Provider_ID int(11) not null,
Provider_Name VARCHAR(45) NOT NULL,
Gender Varchar(25) Not Null,
NPI Varchar(25) Not Null,
Credential Varchar(25) Not Null,
PRIMARY KEY(Provider_ID)
);
CREATE TABLE Eval_Fact_Table
(
Date_ID int(11) not null,
Member_ID int(11) not null,
Provider_ID int(11) not null,
Insurer_ID int(11) not null,
Geography_ID int(11) not null,
Num_Visits int(11) not null,
Eval_Costint int(11) not null,
Eval_Start date not null,
Eval_End date not null,
FOREIGN KEY (Date_ID)
REFERENCES Date_Dim (Date_Id) ON DELETE RESTRICT,
FOREIGN KEY (Member_ID)
REFERENCES Member_Dim (Member_ID) ON DELETE RESTRICT,
FOREIGN KEY (Geography_ID)
REFERENCES Geography_Dim (Geography_ID) ON DELETE RESTRICT,
FOREIGN KEY (Provider_ID)
REFERENCES Proveider_Dim (Provider_ID) ON DELETE RESTRICT,
FOREIGN KEY (Insurer_ID)
REFERENCES Insurer_Dim (Insurer_ID) ON DELETE RESTRICT
);
Error number: 3780; Symbol: ER_FK_INCOMPATIBLE_COLUMNS; SQLSTATE: HY000
Message:Error Code: 3780. Referencing column ‘Geography_ID’ and referenced column ‘Geography_ID’ in foreign key constraint ‘eval_fact_table_ibfk_3’ are incompatible.
I created a schema in MySQL 8.0 named hr. I’m creating two tables: one is locations and the other one departments, which has a foreign key referencing the table locations.
create table hr.locations(
location_id varchar(4) not null unique,
street_address varchar(25) not null,
country_id char(2) not null unique,
primary key(location_id),
foreign key(country_id) references countries(country_id)
);
create table hr.departments(
department_id varchar(4) not null unique,
department_name varchar(30) not null,
manager_id varchar(9) not null unique,
location_id varchar(4) not null unique,
primary key(department_id),
foreign key(location_id) references locations(location_id)
);
When processing it this error appears:
Error Code: 3780. Referencing column ‘location_id’ and referenced
column ‘location_id’ in foreign key constraint ‘departments_ibfk_1’
are incompatible.
Data type is the same for location_id in both tables. I can’t find the mistake.
Ergest Basha
7,9844 gold badges8 silver badges28 bronze badges
asked Nov 14, 2021 at 6:50
3
I encountered a similar problem myself recently. What I did was
-
check the description of my two tables using the MySQL command
SHOW FULL COLUMNS FROM first_table;SHOW FULL COLUMNS FROM second_table;
then (at least in my case):
- I immediately saw that the collation of my reference’s column was different (utf8mb4_unicode_ci in first_table and utf8mb4_0900_ai_ci in second_table).
finally:
- changing the collation in either of the tables was the solution.
ALTER TABLE first_table MODIFY COLUMN column_name varchar(60) COLLATE utf8mb4_0900_ai_ci;
OR
ALTER TABLE second_table
MODIFY COLUMN column_name varchar(60)
COLLATE utf8mb4_unicode_ci;
answered Apr 23 at 9:26
2
I had this problem when one of the properties was marked as Unsigned, and the other was marked as Signed. Changing both properties to match the Unsigned/Signed state fixed the error.
answered Jun 29, 2022 at 17:07
Charlie FishCharlie Fish
18.6k20 gold badges86 silver badges181 bronze badges
Have you tried defining location_id as char(4)? In both tables, of course.
answered Nov 14, 2021 at 7:04
Please check «charter set» and «collation» on the both related fields, if data type is «string/char» type.
There is no such problem («foreign key constraint ‘xxx’ are incompatible») on integer data type (or at least I have not met such incompatibles).
Sometimes created table has different «charter set/collation» from foreign tables (foreign table created by others, restored from old dump etc).
If create new table (charter/collation to be same as foreign table):
CREATE TABLE IF NOT EXISTS `hr`.`locations` (
...
)
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_unicode_ci;
If you have already both tables and would like to change a column:
ALTER TABLE `hr`.`locations`
CHANGE COLUMN `location_id` `location_id` varchar(4)
CHARACTER SET 'utf8mb4'
COLLATE 'utf8mb4_unicode_ci' NOT NULL ;
answered Jan 23, 2022 at 1:51
sankasanka
811 silver badge3 bronze badges
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.
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: , CONSTRAINTidx_nameFOREIGN KEY (employee_id) REFERENCESemployees(id) The index in the foreign key in table isidx_namePlease 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 constraintLet’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: 0Option 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: 0Reason #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 constraintLet’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: 0It 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 constraintIt 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: 0If 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 constraintNope, 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: 0It 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: 0It 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. Error 3780 is a common issue that MYSQL users may encounter when creating or altering a table. This error message typically reads something like «Error Code: 3780. Referencing column ‘COLUMN_NAME’ and referenced column ‘REFERENCED_COLUMN_NAME’ are incompatible.». It occurs when there is a mismatch between the data type or size of a column in a table and the referenced column in a foreign key constraint. Fortunately, this error can be easily resolved with a few simple steps.
Step 1: Identify the source of the error
The first step in resolving Error 3780 is to identify which columns in the table are causing the issue. You can do this by examining the foreign key constraint that is causing the error.
Step 2: Check column data types and sizes
Once you have identified the columns in question, check their data types and sizes. Ensure that they are compatible with the data types and sizes of the corresponding columns in the referenced table. Common errors can occur when attempting to match a VARCHAR column to an INT column, or when attempting to reference a column that is not long enough to contain all possible values of the referencing column.
Step 3: Modify the columns
If the data type or size of the column is causing the issue, modify the columns to be compatible with the referenced column. For example, you may need to alter a VARCHAR column to a CHAR column to ensure that it is the same size as the referenced column. Alternatively, if you need to reference a column that is too short, increase its length to accommodate all possible values of the referencing column.
Step 4: Check the referenced columns
If the columns in the table are not the source of the error, check the referenced columns in the foreign key constraint. Ensure that they are also compatible with the columns in the referencing table.
Step 5: Modify the referenced columns
If the referenced columns in the foreign key constraint are not compatible with the referencing columns, modify them to match the referencing columns.
Step 6: Apply the changes
After modifying the columns and/or foreign key constraints, attempt to create or alter the table again. If the changes have resolved the issue, the table should create or alter successfully.
In conclusion, Error 3780 in MYSQL can be frustrating, but it can also be easily resolved with a few simple steps. Ensure that the columns in the table and foreign key constraints are compatible, make any necessary modifications to the column data types and sizes and apply the changes. With this guide, you should be able to solve Error 3780 without much difficulty.