MySQL 复合键作为外键（sql）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9780163/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Composite key as foreign key (sql)
提问by dgamma3
here are my two tables of concern:
这是我关注的两个表：
CREATE TABLE IF NOT EXISTS `tutorial` (
`beggingTime` time NOT NULL,
`day` varchar(8) NOT NULL,
`tutorId` int(3) NOT NULL,
`maxMembers` int(2) NOT NULL,
`minMembers` int(1) NOT NULL,
PRIMARY KEY (`beggingTime`,`day`,`tutorId`),
KEY `tutorId` (`tutorId`)
)
CREATE TABLE IF NOT EXISTS `group` (
`groupId` tinyint(3) NOT NULL AUTO_INCREMENT,
`status` varchar(20) NOT NULL,
`groupName` varchar(50) NOT NULL,
PRIMARY KEY (`groupId`)
)
I would like to create a field in 'group' that would link to the composite unique keys in 'tutorial'. So I guess my question is, how do I relate these tables? do I have to to create foreign keys field in 'group' for each primary key in 'tutorial'?
我想在“组”中创建一个字段，该字段将链接到“教程”中的复合唯一键。所以我想我的问题是，我如何关联这些表？我是否必须为“教程”中的每个主键在“组”中创建外键字段？
回答by Justin Pihony
Per the mySQL documentationyou should be able to set up a foreign key mapping to composites, which will require you to create the multiple columns.
根据 mySQL 文档，您应该能够设置到组合的外键映射，这将要求您创建多个列。
Add the columns and put this in your grouptable
添加列并将其放入group表格中
FOREIGN KEY (`beggingTime`,`day`,`tutorId`)
REFERENCES tutorial(`beggingTime`,`day`,`tutorId`)
As Steven has alluded to in the below comments, you SHOULD try to re-architect this so that the tutorial table uses an actual primary key (even if it is just an identity surrogate key). This will allow for greater performance as SQL was built for this type of relationship, not composite.
正如史蒂文在下面的评论中提到的，您应该尝试重新构建它，以便教程表使用实际的主键（即使它只是一个身份代理键）。这将允许更高的性能，因为 SQL 是为这种类型的关系构建的，而不是复合的。
回答by kasavbere
1] rewrite the first table: by putting tutorIdfirst, it is automatically a key all by itself. In fact, all but the last of the composite columns becomes a key.
1] 重写第一个表：通过tutorId首先放置，它本身自动成为一个键。事实上，除了最后一个组合列之外的所有列都成为键。
CREATE TABLE IF NOT EXISTS `tutorial` (
`beggingTime` time NOT NULL,
`day` varchar(8) NOT NULL,
`tutorId` int(3) NOT NULL,
`maxMembers` int(2) NOT NULL,
`minMembers` int(1) NOT NULL,
PRIMARY KEY mykey (`tutorId`,`beggingTime`,`day`)
)
2] Having so many indexes is very expensive for heavy write tables. So consider an additional field in tutorial to use as a foreign key; perhaps an auto_increment record_id. Give it some thoughts.
2] 对于繁重的写表来说，拥有如此多的索引是非常昂贵的。因此，请考虑教程中的附加字段用作外键；也许是 auto_increment record_id。给它一些想法。
