8

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.

dieend
  • 2,231
  • 1
  • 24
  • 29
user12271400
  • 81
  • 1
  • 1
  • 2
  • Your code works fine in all versions of MySQL (5.6, 5.7, 8.0) that I tried on dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=57e5f8aed10c8575152dcf0ca868cb05 – Nick Oct 24 '19 at 23:40

3 Answers3

11

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
  • 538,548
  • 86
  • 673
  • 828
2

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.

1

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;
Daniel Duarte
  • 51
  • 4