Mysql创建两个外键引用主键的表

Question

我有两个 mysql 表。第一个是使用以下代码创建的:

create table project(
project_id int not null auto_increment,
project_name varchar(30)
primary key(project_id));

第二张表:

create table matches(
match_id int not null auto_increment,
match_name varchar(30),
project_id int(4) foreign key (project_id) references projects(project_id));

这两个命令都可以正常工作。我想将第一个表中的 project_name 列添加到第二个表中。我尝试使用

alter table projects drop primary key, add primary key(project_id, project_name);

然后

alter table matches add column project_name varchar(30), add foreign key (project_name) references projects(project_name);

但是出现如下错误:

ERROR 1005 (HY000): Can't create table 'matches.#sql-55e_311' (errno: 150)

如何将第一个表中的两列都包含到第二个表中。我的第二张表目前的结构如下:

+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| match_id   | int(11)     | NO   | PRI | NULL    | auto_increment |
| match_name | varchar(30) | YES  |     | NULL    |                |
| project_id | int(4)      | NO   | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+

我想将 project_name 添加为第二个表中的第四列。

Answer 1

To use a compound Primary Key as Foreign Key, you'll have to add the same number of columns (that compose the PK) with same datatypes to the child table and then use the combination of these columns in the FOREIGN KEY definition.

在这里查看相关帖子 https://stackoverflow.com/a/10566463/4904726

所以试试这个方法

alter table matches add foreign key (project_id, project_name) references projects(project_id, project_name);