Error when foreign referencing in mySQL (Error 3780)

user_12271400 0
Viewed 23303

I am making two tables and I want the personOne column from table b to reference the person column on table a, but for some reason it doesn't work.

I have the below code as an example:

create table a(
person varchar(20),
cost varchar(10) not Null
)character set latin1
collate latin1_general_ci;


create table b(
personOne varchar(20),
personTwo varChar(2) not null,
key person_index (personOne),
CONSTRAINT C FOREIGN KEY (personOne) references a(person)
) engine=InnoDB default charset=latin1;

It is telling me an error:

Error Code: 3780. Referencing column 'personOne' and referenced column 'person' in foreign key constraint 'C' are incompatible.

I tried to to set table a engine to InnoDB, but that didn't work. I researched the problem more but couldn't figure out how to fix it.

foreign-keys mysql sql
3 Answers

The two columns must have the same collation. I'm guessing your table b is using the default collation for old versions of MySQL, which is latin1_swedish_ci.

You might like to review this checklist for foreign keys: https://stackoverflow.com/a/4673775/20860

I suggest the best choice is to declare both a and b tables with character set utf8mb4 and collation utf8mb4_unicode_520_ci if your version of MySQL is new enough to support it.

Bill_Karwin_208600

Here I had to first define a primary key in a:

ALTER TABLE a ADD PRIMARY KEY (person);

And pass the COLLATE value of a:

CREATE TABLE b (
...
)ENGINE=InnoDB 
    DEFAULT CHARSET=latin1 
    COLLATE latin1_general_ci;
user_152693570

I got the same error code. Although it may not help your situation, my problem was;

The ID column in all my tables is set as INT UNSIGNED but I was using a foreign key of type INT signed.

Admin1
Related