mysql 主键引用外键_如何使用MySQL引用外键的复合主键

I'm attempting to setup a foreign key in table cell_lines that will reference the topographic_region column of the composite primary key in table topographic_regions.

Each time I run the last three lines of code trying to add the foreign key, I receive Error Code 1215: cannot add foreign key constraint.

Now, the foreign key column name (topographic_region) in cell_lines only matches one of the composite primary key column names in topographic_regions, the other composite primary key column name being topographic_region_id. Do I usually need to address both components of a composite primary key when creating a foreign key?

A follow up problem is that I've actually already tried addressing both components of a composite primary key using a composite foreign key constraint, and I was still presented with an Error Code 1215: cannot add foreign key constraint.

What can I do to solve this problem, and is there anymore information you would like me to provide in order to do so? I'm happy to respond.

Thanks for reading. I'm very new to mySQL.

create table topographic_regions(

topographic_regions_id int not null auto_increment,

topographic_region int(10),

karyotypes varchar(255),

constraint pk_topographicID primary key (topographic_regions_id, topographic_region)

);

create table cell_lines(

cell_lines_id int not null auto_increment,

cell_line varchar(50),

topographic_region int(10),

constraint pk_cellID primary key (cell_lines_id, cell_line)

);

alter table cell_lines

add foreign key (topographic_region)

references topographic_regions(topographic_region);

解决方案

This is the problem with composite PKs. In fact, your autonumber topographic_region_id will be unique and you should use that for the PK, and the FK. topographic_region sounds like it is also unique so you should add a unique index to it.