Error when foreign referencing in mySQL (Error 3780)

Question

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.

Answer 1

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.

Answer 2

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;