Composite key as foreign key (sql) Composite key as foreign key (sql) sql sql

Composite key as foreign key (sql)


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.


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.