In addition to your primary key in the Address table (on Id), you should also declare another key constraint, a UNIQUE constraint, on (Id,UserId).
ALTER TABLE Address ADD CONSTRAINT UQ_Address_UserCheck UNIQUE (Id,UserID)
You can then either replace your existing FKs from Order to address, or add additional ones, that check both columns
ALTER TABLE Order ADD CONSTRAINT
FK_Order_DeliveryAddress_UserCheck FOREIGN KEY (DeliveryAddressID,UserID)
references Address (Id,UserId)
As I say, you can add these all as additional constraints, if you want to.
So, with some slight naming tweaks, your tables are rendered as this:
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)
)
And trying it our with some inserts that should work, except for the last (where there's a user/address mismatch), it works:
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
Results:
(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.
