sql server set complex constraints
CREATE FUNCTION dbo.fnIsAddressOwner (
@CustomerId int,
@AddressId int
)
RETURNS tinyint
AS
BEGIN
DECLARE @Result tinyint
IF EXISTS(SELECT * FROM CustomerAddresses WHERE CustomerId=@CustomerId and AddressId=@AddressId)
SET @Result= 1
ELSE
SET @Result= 0
RETURN @Result
END
CREATE TABLE Customers (
CustomerId int,
PreferredAddressId int,
CONSTRAINT ckPreferredAddressId CHECK (
dbo.fnIsAddressOwner(CustomerId, PreferredAddressId) = 1)
)
)