How to avoid that indirect references that can cause mismatches in the DB

Running SQL-server 2005 and having a database model, in a simplified version, that looks like the image below.

The model is 开发者_运维问答working pretty well but my problem is when I would like to add a reference between Subscriptions and Addresses or Ports. If I create an ordinary FK between Subscriptions.port_id and Ports.id it will be possible to like to a Port/Addresses that has a reference to another Customer than the one references by the Subscription.

I would like to know if it's possible to avoid this problem or if I'll have to handle the problem in the code and with the risk of inconsistent data?

Notes The addresses and ports are pre-populate and exists before the Customers are created

Subscriptions Some subscription will be connected to an Address, some to Ports and some Subscriptions will only have the connection to the Customer.

I would like to know if it's possible to avoid this problem or if I'll have to handle the problem in the code and with the risk of inconsistent data?

You model violates 3NF.

Don't store address_id and customer_id in subscriptions, since they are uniquely defined by port_id.

Instead, store only port_id and get address_id and customer_id through joins.

1) What Quassnoi said

2) If, for whatever reason, you do want to maintain the current structure of the tables:

ALTER TABLE Addresses ADD CONSTRAINT UQ_Addresses_WithCustomers (Id,Customer_ID)

ALTER TABLE Ports ADD CONSTRAINT UQ_Ports_WithAddresses (Id,Address_ID)

These add superkeys to these two tables, so that they can be referenced by foreign key constraints

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)

These foreign keys now enforce that you're matching a row in the addresses table that matches both the address_id and the client_id.

And also, presumably you don't have this yet:

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

Because otherwise you could have a null address_id and a non-null port_id in the subscriptions table, and that isn't checkable (the foreign key constraints wouldn't apply because address_id is null)

Re: Notes The addresses and ports are pre-populated and exist before the Customers are created

I'd create separate tables for these, with no nullable columns, and then introduce new tables (ClientAddresses, ClientPorts) that have foreign keys to these Address, Ports and Client tables. Then change the above to have Subscriptions reference the ClientAddresses/Ports tables instead. In short, the only table I'd leave with nullable columns would be the subscriptions table, and the above constraints should be sufficient there. So you'd have:

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)
)

with UQ_ClientAddresses_WithClients being used as the target for the foreign key relationship from subscriptions.