SQL Server未校验多列外键全部字段，此行为是否正常？

测试数据导入逻辑时发现，SQL Server在多列外键场景下允许插入本应无效的数据：当仅插入第二列外键字段、第一列为NULL时，数据不符合引用表规则却能成功插入。

场景示例 #

创建三张表：country（国家表）、stateprov（州/省表，关联国家表）、person（人员表，同时关联国家表和州/省表）。测试发现：

• 插入不存在的国家时，操作报错（符合预期）
• 插入存在的国家但不存在的州时，操作报错（符合预期）
• 插入国家为NULL、州为'NOTREAL'时，操作被允许（不符合预期，因为stateprov表中既无国家为NULL的记录，也无州为'NOTREAL'的记录）

完整测试SQL代码 #

DROP TABLE IF EXISTS person;
DROP TABLE IF EXISTS stateprov;
DROP TABLE IF EXISTS country;
GO

/* CREATE a country table. */
CREATE TABLE [dbo].[country]
(
    [country_code] [nvarchar](32) NOT NULL,
    [caption] [nvarchar](50) NULL

    CONSTRAINT [PK_country] 
        PRIMARY KEY CLUSTERED ([country_code] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/* CREATE a stateprov table. Key is both country and state. */
CREATE TABLE [dbo].[stateprov]
(
    [country_code] [nvarchar](32) NOT NULL,
    [stateprov_code] [nvarchar](32) NOT NULL,
    [caption] [nvarchar](256) NULL

    CONSTRAINT [PK_stateprov] 
        PRIMARY KEY CLUSTERED ([country_code] ASC, [stateprov_code] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

/* Single part key to reference the country table. */
ALTER TABLE [dbo].[stateprov] WITH CHECK 
    ADD CONSTRAINT [FK_stateprov_country] 
        FOREIGN KEY([country_code]) REFERENCES [dbo].[country] ([country_code])
GO

ALTER TABLE [dbo].[stateprov] CHECK CONSTRAINT [FK_stateprov_country]
GO

/* Finally, our person table. */
CREATE TABLE [dbo].[person]
(
    [contact_code] [int] IDENTITY(1,1) NOT NULL,
    [country_code] [nvarchar](32) NULL,
    [stateprov_code] [nvarchar](32) NULL

    CONSTRAINT [PK_person] 
        PRIMARY KEY CLUSTERED ([contact_code] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] 
GO

/* Single part key to reference the country table. */
ALTER TABLE [dbo].[person]  WITH CHECK 
    ADD CONSTRAINT [FK_person_country] 
        FOREIGN KEY([country_code]) REFERENCES [dbo].[country] ([country_code])
GO

ALTER TABLE [dbo].[person] CHECK CONSTRAINT [FK_person_country]
GO

/* Two part key that points back to the stateprov table, including country and state columns. */
ALTER TABLE [dbo].[person] WITH CHECK 
    ADD CONSTRAINT [FK_person_stateprov] 
        FOREIGN KEY([country_code], [stateprov_code])
            REFERENCES [dbo].[stateprov] ([country_code], [stateprov_code])
GO

ALTER TABLE [dbo].[person] CHECK CONSTRAINT [FK_person_stateprov]
GO

/* Insert a single valid country, and a single value state. */
INSERT INTO country (country_code) VALUES ('US');
INSERT INTO stateprov (country_code, stateprov_code) VALUES ('US', 'CA');
GO

/* This will fail. The country is not real. */
INSERT INTO person (country_code) VALUES ('NOTREAL')
GO

/* This will fail; the country is real, but the state is not. */
INSERT INTO person (country_code, stateprov_code) 
VALUES ('US', 'NOTREAL')
GO

/* I would expect this to fail - BUT IT DOES NOT FAIL. THIS IS ALLOWED!!?? */
INSERT INTO person (stateprov_code) 
VALUES ('NOTREAL')

疑问 #

1. 这是SQL Server的预期行为吗？
2. 如何让SQL Server按预期强制校验？

答案 #

1. 这是SQL Server的预期行为 #

SQL Server的外键约束默认采用MATCH SIMPLE规则：只要外键列中存在任意一个NULL值，就会跳过整个外键约束的校验逻辑。当person表中country_code为NULL、stateprov_code为'NOTREAL'时，因为存在NULL值，FK_person_stateprov约束不会触发校验，所以插入操作被允许。

2. 实现严格校验的方法 #

要让SQL Server模拟MATCH FULL的行为（即要么外键列全为NULL，要么全不为NULL且符合引用规则），可以通过添加CHECK约束来限制字段的NULL组合，结合原有外键约束实现完整校验。

添加CHECK约束的SQL代码如下：

ALTER TABLE [dbo].[person] WITH CHECK
    ADD CONSTRAINT [CHK_country_state_multipart]
        CHECK (
                (country_code IS NULL AND stateprov_code IS NULL) OR 
                (country_code IS NOT NULL AND stateprov_code IS NOT NULL) OR 
                (country_code IS NOT NULL AND stateprov_code IS NULL)
            );

该约束允许三种合法的字段组合：

• 国家和州/省代码都为NULL
• 国家代码非空、州/省代码非空（此时会触发FK_person_stateprov外键校验）
• 国家代码非空、州/省代码为空（此时会触发FK_person_country外键校验）

通过这个CHECK约束，就能避免出现“国家为NULL但州/省非空”的无效数据插入。

内容的提问来源于stack exchange，提问作者Justavian