Composite key as foreign key (sql)

Question

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

do you want 1:1, 1:n, n:1 or n:m relationship? i.e. how many tutorials for how many groups? — Aprillion, Mar 20 '12 at 01:27
@Aprillion Fine question. Since he said "I would like to create a field in 'group' that would link to ... 'tutorial'.", I think we conclude that he wants at most one tutorial for a group. So one (or zero) tutorials, many groups. — lmat - Reinstate Monica, Jun 01 '23 at 20:32

Justin Pihony · Accepted Answer · 2012-03-20T01:30:20.007

39

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.

edited Mar 20 '12 at 01:30

answered Mar 20 '12 at 01:14

Justin Pihony

66,056
18
147
180

5

While it may be possible, this has got to have a severe performance penalty. Primary keys mapped to foreign keys is pretty much the industry standard. – Steve Wellens Mar 20 '12 at 01:27
@SteveWellens Thanks, I updated my answer based on your comment, as you are right and I should have mentioned that along with the direct answer in the first place – Justin Pihony Mar 20 '12 at 01:30
2

WARNING: The referenced columns have to be in the same order that they key was created in. To get the correct order, right-click on the foreign key (under the keys folder when you expand the table in SSMS) and script create to clipboard. Paste. Make sure your referenced columns are in the same order as your key. – Trevor Dec 27 '13 at 19:27
6

I apologize for commenting on a 6 year old answer but I don't understand why the solution might cause a "severe performance penalty" or why a composite primary key is not considered an "actual primary key." Shouldn't a composite primary key have very similar performance to a single primary key of the same size? – nwarp May 24 '18 at 10:06
4

@nwarp, I totally agree with you. That part of the answer is something between misconception and urban myth. – ypercubeᵀᴹ Oct 25 '18 at 20:15
@nwarp I don't really know the reason, probably it's fue some optimization performed by engine on the background, or the way multiple-key indexes are created and searched, but it does affect performance. Create a 50-100 million row, make the join with another table with like a million rows. For the first experiment, use a multiple index, for the second, a generated autoincremental-id as FK. Haven't done this check for several years, so maybe the scenario is different now. – DGoiko Apr 22 '20 at 23:32

score 0 · Answer 2 · answered Mar 20 '12 at 01:35

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.

Composite key as foreign key (sql)

2 Answers2

Linked

Related