对于复合外键，是否/为什么对于具有主键的列组合需要引用表中的复合UNIQUE约束? [英] For a composite foreign key, is a/why is a composite UNIQUE constraint in the referenced table required for a column combination with a primary key?

问题描述

我有一个关于明确定义事物唯一性的问题.这涉及复合外键的创建.我在下面创建了一个示例，试图使我的问题尽可能清楚(为了方便测试，我添加了一些数据插入).

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

[Table3]中[Table1ID]和[Table2ID]的每个组合必须在[Table2]中具有匹配的组合(我假设[Table1ID]和[Table2ID]的两个FOREIGN KEY将是多余的，如果复合材料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]中不唯一防止[Table2]中的[ID]和[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])部分对我来说似乎是多余的，但似乎必须明确定义[Table2]中[Table1ID]的唯一性，以便SQL Server允许在<中创建复合外键c7>.

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.

这篇关于对于复合外键，是否/为什么对于具有主键的列组合需要引用表中的复合UNIQUE约束?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！