复合键作为外键(sql)

2021-11-20 00:00:00 sql mysql phpmyadmin

这是我关注的两个表:

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'?

推荐答案

根据 mySQL 文档，您应该能够设置到组合的外键映射，这将需要您创建多个列.

Per the mySQL documentation you should be able to set up a foreign key mapping to composites, which will require you to create the multiple columns.

添加列并将其放入您的 group 表

Add the columns and put this in your group table

FOREIGN KEY (`beggingTime`,`day`,`tutorId`) REFERENCES tutorial(`beggingTime`,`day`,`tutorId`)

正如 Steven 在下面的评论中提到的，您应该尝试重新构建它，以便教程表使用实际的主键(即使它只是一个身份代理键).这将提高性能，因为 SQL 是为这种类型的关系构建的，而不是复合关系.

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.

相关文章