向Tested_By插入数据时为何触发Product_2外键约束错误？

问题背景 #

我正在使用Microsoft SQL Azure实现数据表及相关查询。

表结构定义 #

Product_1表

create table Product_1 (
    product_id integer not null,
    date_produced varchar(255) not null,
    time_spent integer not null,
    size integer not null,
    software_name varchar(255) not null,
    PRIMARY KEY (product_id)
)

Employee1表

create table Employee1 (
    employee_name varchar(255) not null,
    address varchar(255) not null,
    salary integer not null,
    product_type varchar(255) not null,
    PRIMARY KEY NONCLUSTERED(employee_name)
)

Tested_By关联表

create table Tested_By (
    product_id INTEGER not null,
    employee_name varchar(255) not null,
    PRIMARY KEY (product_id),
    FOREIGN KEY (employee_name) REFERENCES Employee1(employee_name),
    FOREIGN KEY (product_id) REFERENCES Product_1(product_id), 
    FOREIGN KEY (product_id) REFERENCES Product_2(product_id), 
    FOREIGN KEY (product_id) REFERENCES Product_3(product_id) 
)

已执行插入操作 #

INSERT INTO Product_1 VALUES (2, 'a', 4, 5, 'd'), (3,'d',3,3,'d');

INSERT INTO Quality_Controller VALUES ('Bob', 'd', 3, 'd'), ('Alice', 'd', 5, 'd'), ('Joe','d',3,'d');

错误场景 #

执行以下插入语句时：

INSERT INTO Tested_By VALUES (2, 'Bob')

触发错误：

The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Tested_By__produ__05D8E0BE". The conflict occurred in database "[REDACTED]", table "dbo.Product_2", column 'product_id'.

疑问 #

原本预期该插入操作可以成功，因为product_id=2存在于Product_1表，employee_name='Bob'存在于Employee1表，但实际触发了Product_2的外键约束错误，请问原因是什么？

补充说明：Tested_By是多对一关系（多个产品由一名员工测试），因此将product_id设为主键，product_id和employee_name均为外键。

原因分析 #

你给Tested_By表的product_id字段定义了三个独立的外键约束，分别关联Product_1、Product_2、Product_3的product_id。在SQL Server（包括Azure SQL）中，每个外键约束都会被单独校验，也就是说：

• 插入的product_id必须同时存在于Product_1、Product_2、Product_3这三张表中，才能通过所有外键约束的校验
• 你只在Product_1中插入了product_id=2，但Product_2中没有这个值，所以触发了Product_2对应的外键约束冲突

这和你“仅涉及Product_1”的预期不符，因为当前的表设计要求product_id必须在三张产品表中都存在，而不是只存在于其中一张。

另外补充：你的表设计存在逻辑偏差——如果Tested_By是“多个产品由一名员工测试”的多对一关系，那把product_id设为主键意味着每个产品只能被一名员工测试，这是一对一的测试关系，和你描述的多对一（多个产品对应一个员工）不符。如果要实现多对一，应该把product_id和employee_name组合作为主键（或者给表添加独立的自增主键），这样同一个员工可以对应多个产品。

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