对于复合外键，与主键的列组合是否需要/为什么引用表中的复合 UNIQUE 约束?

2022-01-20 00:00:00 foreign-keys tsql sql-server unique-constraint

我有一个关于明确定义某事物的独特性的问题.这与复合外键的创建有关.我在下面创建了一个示例，以尝试使我的问题尽可能清晰(为了便于测试，我添加了一些数据插入).

I have a question regarding explicitly defining of the uniqueness of something. This relates to the creation of a composite foreign key. I've created an example below to try and make my question as clear as possible (I've included some data inserts for ease of testing).

[Table1] 的每个条目都必须有一个唯一的 [Name].

Each entry for [Table1] must have a unique [Name].

CREATE TABLE [Table1]
(
    [ID]    INT IDENTITY            NOT NULL PRIMARY KEY,
    [Name]  NVARCHAR(255) UNIQUE    NOT NULL CHECK(LTRIM(RTRIM([Name])) <> '')
);

INSERT INTO [Table1]([Name])
VALUES
('Name 1'),
('Name 2'),
('Name 3'),
('Name 4'),
('Name 5'),
('Name 6'),
('Name 7')

[Table2] 中的每个 [Value] 对于每个 [Table1ID] 都必须是唯一的.

Each [Value] in [Table2] must be unique for each [Table1ID].

CREATE TABLE [Table2]
(
    [ID]        INT IDENTITY    NOT NULL    PRIMARY KEY,
    [Table1ID]  INT             NOT NULL    FOREIGN KEY REFERENCES [Table1]([ID]),
    [Value]     NVARCHAR(255)   NOT NULL    CHECK(LTRIM(RTRIM([Value])) <> ''),

    --UNIQUE([ID], [Table1ID]),
    UNIQUE([Table1ID], [Value])
);

INSERT INTO [Table2]([Table1ID], [Value])
VALUES
(1, 'Entry 1'),
(1, 'Entry 2'),
(1, 'Entry 3'),
(1, 'Entry 4'),
(3, 'Entry 5'),
(3, 'Entry 6'),
(3, 'Entry 7')

[Table1ID] 和 [Table2ID] 中的每个组合 [Table3] 必须在 [Table2] 中具有匹配的组合 (我假设 [Table1ID] 和 [Table2ID] 的两个 FOREIGN KEY 将是多余的，如果复合外键是否到位?).

Each combination of [Table1ID] and [Table2ID] in [Table3] must have a matching combination in [Table2] (I'm assuming that the two FOREIGN KEYs for [Table1ID] and [Table2ID] would be superfluous if the composite FOREIGN KEY is in place?).

CREATE TABLE [Table3]
(
    [ID]        INT IDENTITY    NOT NULL,
    [Table1ID]  INT             NOT NULL    FOREIGN KEY REFERENCES [Table1]([ID]),
    [Table2ID]  INT             NOT NULL    FOREIGN KEY REFERENCES [Table2]([ID]),

    FOREIGN KEY ([Table2ID], [Table1ID]) REFERENCES [Table2](ID, [Table1ID])
);

INSERT INTO [Table3]([Table2ID], [Table1ID])
VALUES
(5, 3)

[Table3] 中的复合 FOREIGN KEY 约束是问题所在.如果 [Table2] 中被注释掉的 UNIQUE 约束没有被注释，则 [Table3] 可以成功创建.如果不是，[Table3] 的创建将失败，提示引用的表中没有与外键中的引用列列表匹配的主键或候选键".

That composite FOREIGN KEY constraint in [Table3] is the problem. If that commented-out UNIQUE constraint in [Table2] is uncommented, [Table3] can be created successfully. If it is not, the creation of [Table3] will fail saying "There are no primary or candidate keys in the referenced table that match the referencing column list in the foreign key".

我了解键的唯一性需要，但是因为 [Table2] 的 [ID] 列是 PRIMARY KEY并且永远是唯一的，为什么 [Table1ID] 列在 [Table2] 中不是唯一的，会阻止 [ID] 和 <[Table2] 中的code>[Table1ID] 是否唯一?

I understand the need for uniqueness with regards to keys, however as the [ID] column for [Table2] is a PRIMARY KEY and will always be unique, why would the [Table1ID] column not being unique in [Table2] prevent any combination of [ID] and [Table1ID] in [Table2] from being unique?

基本上， UNIQUE([ID], [Table1ID]) 部分对我来说似乎是多余的，但似乎 [Table1ID] 在 的唯一性>[Table2] 必须显式定义，以便 SQL Server 允许在 [Table3] 中创建复合外键.

Basically, the UNIQUE([ID], [Table1ID]) part seems pretty superfluous to me, yet it seems that the uniqueness of [Table1ID] in [Table2] must be explicitly defined in order for SQL Server to allow the creation of the composite foreign key in [Table3].

真的是这样吗?为了允许上述情况，需要这种约束，无论它看起来多么多余?还是我错过了什么?

Is that actually the case? That this constraint, however superfluous it may seem, is required in order to allow the above? Or am I missing something?

推荐答案

实际上更多的是与关系数据库的理论方面有关.

It's more to do with the theoretical side of relational databases, actually.

在其父表中引用的外键不是一组任意的列，无论它们多么独特；它引用一个键 - 主键或备用键.并且这个键必须明确声明.

What foreign key references in its parent table is not an arbitrary set of columns, however unique they might be; it references a key - either primary or alternate. And this key must be clearly declared as such.

相关文章