如何在 SQL Server 中创建复合主键（T-SQL 示例）

发布于

复合主键是由多列组成的主键。微软通常在其文档中将这些称为多列主键。

本文提供了在 SQL Server 中使用 Transact-SQL 创建复合主键的示例。

您可以像创建单个主键一样创建复合主键，不同之处在于，您提供两个或更多列的名称，而不是只指定一列，用逗号分隔。

示例如下：

CONSTRAINT PK_Name PRIMARY KEY (Column1, Column2)

示例 1 – 创建复合主键

下面是使用复合主键的数据库示例。

出于此示例的目的，我将创建一个名为 的数据库：PK_Test

CREATE DATABASE PK_Test;

现在数据库已创建，让我们继续创建表。

USE PK_Test;

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) );

在此示例中，表具有多列主键。在这种情况下，主键中的每一列也是另一个表的主键的外键，但这不是必需的。BandMember

上述数据库设计背后的原因是，音乐家可能是许多乐队的成员。此外，每个乐队可以有很多音乐家。所以我们有一个多对多的关系。这就是创建表的原因 - 它用作表和表之间的交叉引用表。BandMember``Musician``Band

这种特殊情况支持复合主键，因为音乐家是乐队的成员应该是唯一的。换句话说，我们不希望多行音乐家是同一乐队的成员。这将违反数据完整性。当我们试图保持引用完整性时，即使我们曾经在这个表和另一个表之间创建关系（我们在这里这样做），它也可能导致严重破坏。

示例 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 );

示例 3 – 基本查询

现在数据在我们的数据库中，让我们运行一个查询来返回其中一些数据。

下面是一个基本查询：

SELECT CONCAT(m.FirstName, ’ ‘, m.LastName) AS ‘Musician’, b.BandName AS ‘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;

输出：

+------------------+-------------+
| Musician         | Band        |
|------------------+-------------|
| Ian Paice        | Deep Purple |
| Ian Paice        | Whitesnake  |
| Roger Glover     | Deep Purple |
| Roger Glover     | Rainbow     |
| Richie Blackmore | Deep Purple |
| Richie Blackmore | Rainbow     |
| Rod Evans        | Deep Purple |
+------------------+-------------+

因此，正如预期的那样，这仅返回那些在引用表中具有条目的音乐家和乐队。BandMember

示例 4 – 略微修改的查询

下面是上述查询的修改版本，它以不同的方式显示结果：

SELECT b.BandName AS ‘Band’, STRING_AGG(CONCAT(m.FirstName, ’ ‘, m.LastName), ‘, ‘) AS ‘Musicians’ FROM Musician m JOIN BandMember bm ON m.MusicianId = bm.MusicianId JOIN Band b ON b.BandId = bm.BandId AND m.MusicianId = bm.MusicianId GROUP BY b.BandName;

输出：

+-------------+------------------------------------------------------+
| Band        | Musicians                                            |
|-------------+------------------------------------------------------|
| Deep Purple | Ian Paice, Roger Glover, Richie Blackmore, Rod Evans |
| Rainbow     | Roger Glover, Richie Blackmore                       |
| Whitesnake  | Ian Paice                                            |
+-------------+------------------------------------------------------+

此处的结果按波段分组，每个波段的所有音乐家都显示为单个字段中的逗号分隔列表。

为此，我使用 STRING_AGG() 函数来连接音乐家。

复合外键

上面示例的问题在于大多数数据都已过时。其中一些音乐家实际上已经离开了这些乐队。有些人已经离开，然后在以后返回。

我们该如何处理这个问题？

我们可以创建另一个参考表来记录每个音乐家是每个乐队成员的时间段。这样的表需要通过外键引用该表。看到这个表有一个复合主键，我们需要在引用它的新表上使用复合外键。BandMember

有关示例，请参阅如何在 SQL Server 中创建复合外键。该文章使用与上述相同的示例，只是使用了一个额外的表，其中包含引用上述复合主键的复合外键。