如何避免可能导致数据库中不匹配的间接引用

Question

运行 SQL-server 2005 并拥有一个简化版本的数据库模型，如下图所示。

该模型运行得很好，但我的问题是当我想在订阅和地址或端口之间添加引用时。如果我在 Subscriptions.port_id 和 Ports.id 之间创建一个普通的 FK，则可能会喜欢一个端口/地址，该端口/地址引用了另一个客户，而不是订阅所引用的客户。

我想知道是否可以避免这个问题，或者我是否必须在代码中处理该问题并冒着数据不一致的风险？

注释 地址和端口已预先填充并在创建客户

订阅 之前存在 有些订阅将连接到地址，有些订阅将连接到端口，有些订阅将仅连接到客户。

Answer 1

我想知道是否可以避免这个问题，或者我是否必须在代码中处理该问题并冒着数据不一致的风险？

您的模型违反了3NF。

不要在订阅中存储 address_id 和 customer_id，因为它们是由 port_id 唯一定义的。

相反，仅存储 port_id 并通过联接获取 address_id 和 customer_id。

Answer 2

1) Quassnoi 所说的

2) 如果，无论出于何种原因，您确实想要维护表的当前结构：

ALTER TABLE Addresses ADD CONSTRAINT UQ_Addresses_WithCustomers (Id,Customer_ID)

ALTER TABLE Ports ADD CONSTRAINT UQ_Ports_WithAddresses (Id,Address_ID)

这些将超级键添加到这两个表中，以便它们可以通过外键约束引用

ALTER TABLE Subscriptions ADD
    CONSTRAINT FK_Subscription_Addresses (Address_ID,Customer_ID)
    references Addresses (Id,Customer_ID)

ALTER TABLE Subscriptions ADD
    CONSTRAINT FK_Subscription_Ports (Port_ID,Address_ID)
    references Ports (ID, Address_ID)

这些外键现在强制您正在匹配地址表中与address_id 和client_id 都匹配的行。

而且，大概您还没有这个：

ALTER TABLE Subscriptions ADD
    CONSTRAINT CK_Subscription_EnhancedNUllability CHECK
     (Port_ID is null or Address_ID is not null)

因为否则您可能会在订阅表中拥有一个空的address_id和一个非空的port_id，并且这是不可检查的（外键约束将不适用，因为address_id为空）

---

回复：注释地址和端口是预先填充的，并且在创建客户之前就存在

我会为这些创建单独的表，没有可为空的列，然后引入新表（ClientAddresses、ClientPorts）具有这些地址、端口和客户端表的外键。然后更改上述内容，让订阅引用 ClientAddresses/Ports 表。简而言之，我唯一保留可为空列的表是订阅表，并且上述约束应该足够了。因此，您可以：

CREATE TABLE Addresses (
    AddressID int IDENTITY(1,1) not null,
    Name nchar(10) not null,
    constraint PK_Addresses PRIMARY KEY (AddressID),
    constraint UQ_Address_Names UNIQUE (Name)
)

CREATE TABLE ClientAddresses (
    ClientID int not null,
    AddressID int not null,
    constraint PK_ClientAddresses PRIMARY KEY (AddressID) /*This also prevents the same address being assigned to multiple clients */,
    constraint FK_ClientAddresses_Clients FOREIGN KEY (ClientID) references Clients (ID),
    constraint FK_ClientAddresses_Addresses FOREIGN KEY (AddressID) references Addresses (AddressID),
    constraint UQ_ClientAddresses_WithClients UNIQUE (ClientID,AddressID)
)

将 UQ_ClientAddresses_WithClients 用作订阅中外键关系的目标。

Answer 3