A composite foreign key is a foreign key consisting of multiple columns.
This article provides an example of creating a composite foreign key using Transact-SQL in SQL Server.
You can create a composite foreign key just as you would create a single foreign key, except that instead of specifying just one column, you provide the name of two or more columns, separated by a comma.
Like this:
CONSTRAINT FK_FKName FOREIGN KEY (FKColumn1, FKColumn2) REFERENCES PrimaryKeyTable (PKColumn1, PKColumn2)
Example 1 – Create a Composite Foreign Key
Here’s an example of a database using a composite foreign key (and a composite primary key).
For the purposes of this example, I’ll create a database called BandTest:
CREATE DATABASE BandTest;
Now that the database is created, let’s go ahead and create the tables.
USE BandTest; CREATE TABLE Musician ( MusicianId int NOT NULL, FirstName varchar(60), LastName varchar(60), CONSTRAINT PK_Musician PRIMARY KEY (MusicianID) ); CREATE TABLE Band ( BandId int NOT NULL, BandName varchar(255), CONSTRAINT PK_Band PRIMARY KEY (BandId) ); CREATE TABLE BandMember ( MusicianId int NOT NULL, BandId int NOT NULL, CONSTRAINT PK_BandMember PRIMARY KEY (MusicianID, BandId), CONSTRAINT FK_BandMember_Band FOREIGN KEY (BandId) REFERENCES Band(BandId), CONSTRAINT FK_BandMember_Musician FOREIGN KEY (MusicianId) REFERENCES Musician(MusicianId) ); CREATE TABLE MembershipPeriod ( MembershipPeriodId int NOT NULL, MusicianId int NOT NULL, BandId int NOT NULL, StartDate date NOT NULL, EndDate date NULL, CONSTRAINT PK_MembershipPeriod PRIMARY KEY (MembershipPeriodID), CONSTRAINT FK_MembershipPeriod_BandMember FOREIGN KEY (MusicianID, BandId) REFERENCES BandMember(MusicianID, BandId) );
In this example, the BandMember table has a multicolumn primary key. The MembershipPeriod table has a foreign key that references that multicolumn primary key. Therefore, both the primary and foreign key definitions include the columns separated by a comma.
The reasoning behind the above database design is that, a musician could potentially be a member of many bands. Also, each band can have many musicians. So we have a many-to-many relationship. This is why the BandMember table is created – it is used as a cross-reference table between the Musician table and the Band table. In this case, I’ve chosen to use a composite primary key.
But a musician could also be a member of a band on more than one occasion (e.g. a musician might leave a band, only to return later). Therefore, the MembershipPeriod table can be used to record all the periods each musician has been a member of each band. This needs to reference the composite primary key on the BandMember table, and so I need to create a multicolumn foreign key.
Example 2 – Insert Data
Having just run the above code, I can now load the database with data:
INSERT INTO Musician VALUES ( 1, 'Ian', 'Paice' ), ( 2, 'Roger', 'Glover' ), ( 3, 'Richie', 'Blackmore' ), ( 4, 'Rod', 'Evans' ), ( 5, 'Ozzy', 'Osbourne' ); INSERT INTO Band VALUES ( 1, 'Deep Purple' ), ( 2, 'Rainbow' ), ( 3, 'Whitesnake' ), ( 4, 'Iron Maiden' ); INSERT INTO BandMember VALUES ( 1, 1 ), ( 1, 3 ), ( 2, 1 ), ( 2, 2 ), ( 3, 1 ), ( 3, 2 ), ( 4, 1 ); INSERT INTO MembershipPeriod VALUES ( 1, 1, 1, '1968-03-01', '1976-03-15' ), ( 2, 1, 1, '1984-04-01', NULL ), ( 3, 1, 3, '1979-08-01', '1982-01-01' ), ( 4, 2, 1, '1969-01-01', '1973-06-29' ), ( 5, 2, 1, '1984-04-01', NULL ), ( 6, 2, 2, '1979-01-01', '1984-01-01' ), ( 7, 3, 1, '1968-03-01', '1975-06-21' ), ( 8, 3, 1, '1984-04-01', '1993-11-17' ), ( 9, 3, 2, '1975-02-01', '1984-04-01' ), ( 10, 3, 2, '1993-11-17', '1997-05-31' ), ( 11, 3, 2, '2015-01-01', NULL ), ( 12, 4, 1, '1968-03-01', '1969-12-01' );
Example 3 – Basic Query
Here’s an example of a query that could be run against the database:
SELECT CONCAT(m.FirstName, ' ', m.LastName) AS 'Musician', b.BandName AS 'Band', mp.StartDate AS 'Start', mp.EndDate AS 'End' FROM Musician m JOIN BandMember bm ON m.MusicianId = bm.MusicianId JOIN Band b ON b.BandId = bm.BandId AND m.MusicianId = bm.MusicianId JOIN MembershipPeriod mp ON mp.BandId = b.BandId AND mp.MusicianId = m.MusicianId;
Result:
+------------------+-------------+------------+------------+ | Musician | Band | Start | End | |------------------+-------------+------------+------------| | Ian Paice | Deep Purple | 1968-03-01 | 1976-03-15 | | Ian Paice | Deep Purple | 1984-04-01 | NULL | | Ian Paice | Whitesnake | 1979-08-01 | 1982-01-01 | | Roger Glover | Deep Purple | 1969-01-01 | 1973-06-29 | | Roger Glover | Deep Purple | 1984-04-01 | NULL | | Roger Glover | Rainbow | 1979-01-01 | 1984-01-01 | | Richie Blackmore | Deep Purple | 1968-03-01 | 1975-06-21 | | Richie Blackmore | Deep Purple | 1984-04-01 | 1993-11-17 | | Richie Blackmore | Rainbow | 1975-02-01 | 1984-04-01 | | Richie Blackmore | Rainbow | 1993-11-17 | 1997-05-31 | | Richie Blackmore | Rainbow | 2015-01-01 | NULL | | Rod Evans | Deep Purple | 1968-03-01 | 1969-12-01 | +------------------+-------------+------------+------------+
So we can now see which dates each musician was a member of each band, even if they were a member on multiple occasions.
Example 4 – Slightly Modified Query
We could modify the above query to provide the results in a slightly more readable format:
SELECT
CONCAT(m.FirstName, ' ', m.LastName) AS 'Musician',
b.BandName AS 'Band',
STRING_AGG(
CONCAT(FORMAT(mp.StartDate, 'yyyy'), '-', ISNULL(FORMAT(mp.EndDate, 'yyyy'), 'present')), ', ') AS 'Time with the band'
FROM Musician m
JOIN BandMember bm
ON m.MusicianId = bm.MusicianId
JOIN Band b
ON b.BandId = bm.BandId AND m.MusicianId = bm.MusicianId
JOIN MembershipPeriod mp
ON mp.BandId = b.BandId AND mp.MusicianId = m.MusicianId
GROUP BY m.FirstName, m.LastName, b.BandName;
Result:
+------------------+-------------+------------------------------------+ | Musician | Band | Time with the band | |------------------+-------------+------------------------------------| | Ian Paice | Deep Purple | 1968-1976, 1984-present | | Ian Paice | Whitesnake | 1979-1982 | | Richie Blackmore | Deep Purple | 1968-1975, 1984-1993 | | Richie Blackmore | Rainbow | 1975-1984, 1993-1997, 2015-present | | Rod Evans | Deep Purple | 1968-1969 | | Roger Glover | Deep Purple | 1969-1973, 1984-present | | Roger Glover | Rainbow | 1979-1984 | +------------------+-------------+------------------------------------+
This example takes advantage of the STRING_AGG() function to concatenate the various time periods for each musician. This ends up reducing the number of rows required, and allows us to group the time periods together in the same field.
I also take advantage of the ISNULL() function, which allows me to change any NULL values into something more meaningful.
Note that ISNULL() requires that the second argument is of a type that can be implicitly converted to the type of the first argument. In this case, the first argument was originally a date type, which means that I wouldn’t be able to use a string. However, in this case I decided to use the FORMAT() function to format the date value. This function implicitly converts the date value to a string, and therefore I was able to use a string for the second argument.