ERROR 3780: Referencing column and referenced column in foreign key constraint are incompatible

Question

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.

Boreas

I am able to execute the queries perfectly fine? Both tables get created and the foreign key exists? The only other thing I can think of is to make sure both tables have the same collation. – Laraelaraine

WHy is departments.location_id declared UNIQUE? Can't you have more than one department at a location? – Surbased

Does this answer your question? MySQL Creating tables with Foreign Keys giving errno: 150 – Anthropophagi

Answer 1

I encountered a similar problem myself recently. What I did was

1. 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):

2. 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:

3. 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;

Ezequieleziechiele

Answer 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.

Superable

Answer 3

Have you tried defining location_id as char(4)? In both tables, of course.

Horrify

Answer 4

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 ;

Renaud