sql server - 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?

Question

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

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

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

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)

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".

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?

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?

Answer 1

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.