如何使用MySQL引用外键的复合主键 [英] How do I reference a composite primary key with a foreign key using MySQL

问题描述

我正在尝试在表格 cell_lines 中设置外键，引用 topographic_regions 表中复合主键的 topographic_region 。

每次我运行最后三行代码试图添加外键，我收到错误代码1215：不能添加外键约束。

现在， cell_lines 中的外键列名称（ topographic_region ）只匹配 topographic_regions ，另一个复合主键列名称为 topographic_region_id 。在创建外键时，我通常需要解决复合主键的两个组件吗？

后续问题是，我已经尝试解决两个组件一个复合主键使用一个复合外键约束，我仍然被提交一个错误代码1215：不能添加外键约束。

我可以做什么来解决这个问题，是否有更多的信息，你希望我提供为了这样做？我很高兴回应。

感谢您阅读。我对mySQL非常新。

  create table topographic_regions（
 topographic_regions_id int not null auto_increment，
 topographic_region int（10），
 karyotypes varchar（255），
 constraint pk_topographicID主键（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），
约束pk_cellID主键（cell_lines_id，cell_line）
）; 
 
 alter table cell_lines 
添加外键（topographic_region）
引用topographic_regions（topographic_region）; 
  

解决方案

这是组合PK的问题。实际上，您的自动编号topographic_region_id将是唯一的，您应该将其用于PK和FK。 topographic_region听起来像是唯一的，所以你应该添加一个唯一的索引。

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.

这篇关于如何使用MySQL引用外键的复合主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！