sql - 如何使用复合键在 SQL Server 中实现参照完整性？

Question

当我运行底部看到的 SQL 时，为什么返回:

Msg 1776, Level 16, State 0, Line 2
There are no primary or candidate keys in the referenced table 'pricedex.table_a' that match the referencing column list in the foreign key 'FK_delete_from_parent'.

Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

代码:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE table_a 
(
    [column_1] [int] NULL,
    [column_2] [int] NULL
)

CREATE TABLE table_b 
(
    [column_1] [int] NULL,
    [column_2] [int] NULL
)

GO

CREATE NONCLUSTERED INDEX IX_app ON table_a (column_1, column_2)
GO

CREATE NONCLUSTERED INDEX IX_app ON table_b (column_1, column_2)
GO

SET ANSI_PADDING OFF
GO

ALTER TABLE table_b WITH CHECK
ADD CONSTRAINT FK_delete_from_parent 
    FOREIGN KEY (column_1, column_2) REFERENCES table_a (column_1, column_2) 
       ON DELETE CASCADE
GO

Answer 1

您需要将PRIMARY KEY 添加到table_a 并将PK 列更改为NOT NULL:

CREATE TABLE table_a (
    [column_1] [int] NOT NULL,
    [column_2] [int] NOT NULL,
    PRIMARY KEY(column_1, column_2)            -- compound primary key
);

CREATE TABLE table_b (
    [column_pk] [int] NOT NULL PRIMARY KEY,    -- you probably want different pk
    [column_1] [int] NULL,
    [column_2] [int] NULL
);

-- adding foreign key constraint
ALTER TABLE table_b WITH CHECK
ADD CONSTRAINT FK_delete_from_parent FOREIGN KEY (column_1, column_2)
REFERENCES table_a (column_1, column_2) ON DELETE CASCADE;

SqlFiddleDemo

编辑:

Create Foreign Key Relationships :

A foreign key constraint does not have to be linked only to a primary key constraint in another table; it can also be defined to reference the columns of a UNIQUE constraint in another table.

CREATE TABLE table_a (
    [column_1] [int] NOT NULL,           -- with UNIQUE column can be nullable
    [column_2] [int] NOT NULL,
    UNIQUE(column_1, column_2)
    -- anyway this table should have PK
);

SqlFiddleDemo2

请注意，如果列可以为空并且您有 NULL 值，ON DELETE CASCADE 将不会从相应表中删除记录。