如何在 SQL Server 中创建复合外键（T-SQL 示例）
复合外键是由多列组成的外键。
本文提供了在 SQL Server 中使用 Transact-SQL 创建复合外键的示例。
可以像创建单个外键一样创建复合外键，不同之处在于，您提供两个或多个列的名称（用逗号分隔），而不是只指定一列。
示例如下：
CONSTRAINT FK_FKName FOREIGN KEY (FKColumn1, FKColumn2) REFERENCES PrimaryKeyTable (PKColumn1, PKColumn2)
示例 1 – 创建复合外键
下面是使用复合外键（和复合主键）的数据库示例。
出于此示例的目的，我将创建一个名为 的数据库：BandTest
CREATE DATABASE BandTest;
现在数据库已创建，让我们继续创建表。
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) );
在此示例中，表具有多列主键。该表具有引用该多列主键的外键。因此，主键和外键定义都包括用逗号分隔的列。BandMember``MembershipPeriod
上述数据库设计背后的原因是，音乐家可能是许多乐队的成员。此外，每个乐队可以有很多音乐家。所以我们有一个多对多的关系。这就是创建表的原因 - 它用作表和表之间的交叉引用表。在本例中，我选择使用复合主键。BandMember``Musician``Band
但音乐家也可能不止一次成为乐队的成员（例如，音乐家可能会离开乐队，但后来又回来了）。因此，该表可用于记录每个音乐家是每个乐队成员的所有时期。这需要引用表上的复合主键，因此我需要创建一个多列外键。MembershipPeriod``BandMember
示例 2 – 插入数据
刚刚运行了上面的代码，我现在可以用数据加载数据库：
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’ );
示例 3 – 基本查询
下面是可以对数据库运行的查询示例：
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;
输出：
+------------------+-------------+------------+------------+
| 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 |
+------------------+-------------+------------+------------+因此，我们现在可以看到每个音乐家是每个乐队的成员的日期，即使他们多次成为成员。
示例 4 – 略微修改的查询
我们可以修改上面的查询，以稍微更易读的格式提供结果：
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;
输出：
+------------------+-------------+------------------------------------+
| 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 |
+------------------+-------------+------------------------------------+此示例利用 STRING_AGG() 函数连接每个音乐家的不同时间段。这最终减少了所需的行数，并允许我们将时间段分组在同一字段中。
我还利用了 ISNULL() 函数，它允许我将任何 NULL 值更改为更有意义的值。
请注意，这要求第二个参数的类型可以隐式转换为第一个参数的类型。在这种情况下，第一个参数最初是日期类型，这意味着我无法使用字符串。但是，在这种情况下，我决定使用 FORMAT() 函数来格式化日期值。此函数将日期值隐式转换为字符串，因此我能够将字符串用于第二个参数。ISNULL()