检查项目是否没有错误链接 - 这就是“外键约束”的目的是为了？

Question

我现在在 SQLAzure 工作。

我正在设置一个设计，其中每个用户都有多个地址，

当用户下订单时，我想将该订单链接到用户和几个地址。

所以我的桌子看起来像：

用户

• 身份证
• 姓名
• 等等

地址

• 身份证
• 用户 ID（外键）
• 街道
• 等等

订单

• 身份证
• 用户 ID（外键）
• DeliveryAddressId（外键）
• BillingAddressId（外键）
• 等等

有没有办法在 SQL Server 中设置检查，以便用户可以在任何情况下（例如，通过破解 HTML POST）都不要提交带有与所提交的 UserId 不链接的 AddressId 的订单。我已经查看了文档中的“外键约束”，但这似乎并不是我想要的。

任何有关尝试什么或阅读什么教程的建议将不胜感激。

Answer 1

除了 Address 表中的主键（在 Id 上）之外，您还应该在 (Id,UserId) 上声明另一个键约束，即 UNIQUE 约束。

ALTER TABLE Address ADD CONSTRAINT UQ_Address_UserCheck UNIQUE (Id,UserID)

然后，您可以将现有的 FK 从订单替换为地址，或者添加其他 FK，以检查两列

ALTER TABLE Order ADD CONSTRAINT
     FK_Order_DeliveryAddress_UserCheck FOREIGN KEY (DeliveryAddressID,UserID)
     references Address (Id,UserId)

正如我所说，如果您愿意，您可以将这些全部添加为附加约束。

---

因此，通过一些轻微的命名调整，您的表格将呈现如下：

create table Users (
    UserID int IDENTITY(1,1) not null,
    Name varchar(30) not null,
    /* Other columns */
    constraint PK_Users PRIMARY KEY (UserID),
    constraint UQ_User_Names UNIQUE (Name)
)
go
create table Addresses (
    AddressID int IDENTITY(1,1) not null,
    UserID int not null,
    Street varchar(35) not null,
    /* Other columns */
    constraint PK_Addresses PRIMARY KEY (AddressID),
    constraint FK_Addresses_Users FOREIGN KEY (UserID) references Users (UserID),
    constraint UQ_Addresses_UserCheck UNIQUE (UserID,AddressID)
)
go
create table Orders (
    OrderID int IDENTITY (1,1) not null,
    UserID int not null,
    DeliveryAddressID int not null,
    BillingAddressID int not null,
    /* Other columns - there may be other nullability concerns above */
    constraint PK_Orders PRIMARY KEY (OrderID),
    constraint FK_Orders_Users FOREIGN KEY (UserID) references Users (UserID),
    constraint FK_Orders_DeliveryAddresses FOREIGN KEY (DeliveryAddressID) references Addresses (AddressID),
    constraint FK_Orders_BillingAddresses FOREIGN KEY (BillingAddressID) references Addresses (AddressID),
    /* Further constraints - ensure UserID -> AddressID match */
    constraint FK_Orders_DeliveryAddress_UserCheck FOREIGN KEY (UserID,DeliveryAddressID) references Addresses (UserID,AddressID),
    constraint FK_Orders_BillingAddress_UserCheck FOREIGN KEY (UserID,BillingAddressID) references Addresses (UserID,AddressID)
)

并尝试使用一些应该有效的插入，除了最后一个（用户/地址不匹配）之外，它有效：

declare @UID1 int
declare @UID2 int
declare @AID1_1 int
declare @AID1_2 int
declare @AID2_1 int
declare @AID2_2 int
insert into Users (Name)
select 'User1'
set @UID1 = SCOPE_IDENTITY()
insert into Users (Name)
select 'User2'
set @UID2 = SCOPE_IDENTITY()

insert into Addresses (UserID,Street)
select @UID1,'Street1'
set @AID1_1 = SCOPE_IDENTITY()
insert into Addresses (UserID,Street)
select @UID1,'Street2'
set @AID1_2 = SCOPE_IDENTITY()
insert into Addresses (UserID,Street)
select @UID2,'Street1'
set @AID2_1 = SCOPE_IDENTITY()
insert into Addresses (UserID,Street)
select @UID2,'Street2'
set @AID2_2 = SCOPE_IDENTITY()

insert into Orders (UserID,DeliveryAddressID,BillingAddressID)
select @UID1,@AID1_1,@AID1_2 union all
select @UID2,@AID2_1,@AID2_1

insert into Orders (UserID,DeliveryAddressID,BillingAddressID)
select @UID1,@AID1_1,@AID2_1

结果：

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

(2 row(s) affected)
Msg 547, Level 16, State 0, Line 31
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Orders_BillingAddress_UserCheck". The conflict occurred in database "Test", table "dbo.Addresses".
The statement has been terminated.

Answer 2

创建一个 ON INSERT 触发器来执行您想要强制执行的任何其他逻辑。

缺点是用户在尝试使用错误地址时会收到错误消息...为了主动，您也应该在 GUI 中执行此检查。

Answer 3

使用从 Order (UserID, DeliveryAddressID) 到 Address (UserID, ID) 的复合外键可以吗？ （与 BillingAddressID 类似）