Composite key as foreign key (sql)

问题:

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

回答1:

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.

Add the columns and put this in your group table

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.

回答2:

1] rewrite the first table: by putting tutorId first, it is automatically a key all by itself. In fact, all but the last of the composite columns becomes a key.

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.