Complex Check Constraint?

Question

I have a customers table that links to an addresses table through a middle CustomerAddress table. This means that a customer can have many addresses and an address can have many customers. (This is neccessary due to the fact that we deal with spouses and children as seperate customers, and each can have delivery, work, billing and other addresses).

I want a customer to be able to specify a preferred address.

My thought was to create a new column in the customers table that links to a CustomerAddress record.

My problem is- how can I ensure that the selected preferred address is one of that customers addresses?

My thought was to put a Check constraint on the customers.preferredAddress field that checks the given CustomerAddress to see if that records customer ID matches the customer being updated.

Is this possible? I have only ever used Check constraints to check simple stuff like (Value > 0) etc.

Thanks for your help

Ragged

Why on the customer table? How about a new column on the customer address record that is 'preferred address' and a simple y/n (or bit) to define a yes or no (or null for no)? If it's on the customeraddress table, the relation that it is one of the customers addresses is enforced. – Bashemath

But how could I stop one customer having multiple preferred addresses? – Ragged

Answer 1

Write a UDF for verifying address ownership, then reference that UDF from a check constraint.

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

Shingles

If check constraints don't work for you then you can also perform the check in a trigger. If validation fails, roll back the transaction. – Shingles

Of course, nothing in the above prevents an UPDATE CustomerAddresses set CustomerId = <new value> where CustomerId = <old value> and now the "constraint" is violated. – Catchment