Ensure that one and only one default is defined for a set

Question

I have a customer table that has a one to many relationship with an address table. I want to constrain the database so that a customer with addresses will always have one (and only one) default address.

I can quite easily add a constraint to ensure that there is only ever one default address for each customer. I am however struggling on how to apply a constraint that ensures that an address is always marked as default.

To summarize:

• A customer is not required to have any addresses.
• If a customer has addresses there must be a default address.
• There must only be one default address per customer.

Here is an example of the problem and some 'unit' tests. I am using a link table to join customers and addresses.

CREATE TABLE Customer
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

CREATE TABLE [Address]
(
    Id INT PRIMARY KEY,
    Address VARCHAR(500) NOT NULL
)

CREATE TABLE CustAddress
(
    CustomerId INT,
    AddressId INT,
    [Default] BIT NOT NULL,
    FOREIGN KEY (CustomerId) REFERENCES Customer(Id),
    FOREIGN KEY (AddressId) REFERENCES [Address](Id)
)

INSERT INTO Customer VALUES (1, 'Mr Greedy')

INSERT INTO [Address] VALUES (1, 'Roly-Poly House, Fatland')
INSERT INTO [Address] VALUES (2, 'Giant Cottage, A Cave')

-- Should succeed
INSERT INTO CustAddress VALUES (1, 1, 1)
INSERT INTO CustAddress VALUES (1, 2, 0)

DELETE FROM CustAddress

-- Should fail as no default address set
INSERT INTO CustAddress VALUES (1, 1, 0)

DELETE FROM CustAddress

-- Should fail as we end up with no defualt address set
INSERT INTO CustAddress VALUES (1, 1, 1)
INSERT INTO CustAddress VALUES (1, 2, 0)
UPDATE CustAddress SET [Default] = 0 WHERE CustomerId = 1 AND AddressId = 1

DELETE FROM CustAddress

-- Should fail as we end up with no defualt address set
INSERT INTO CustAddress VALUES (1, 1, 1)
INSERT INTO CustAddress VALUES (1, 2, 0)
DELETE FROM CustAddress WHERE CustomerId = 1 AND AddressId = 1

Answer 1

How about changing the schema to

CREATE TABLE Customer
(
    Id INT PRIMARY KEY,
    Name VARCHAR(100) NOT NULL
)

CREATE TABLE [Address]
(
    Id INT PRIMARY KEY,
    Address VARCHAR(500) NOT NULL
)


CREATE TABLE CustDefaultAddress
(
    CustomerId INT PRIMARY KEY, /*Ensures no more than one default*/
    AddressId INT,
    FOREIGN KEY (CustomerId) REFERENCES Customer(Id),
    FOREIGN KEY (AddressId) REFERENCES [Address](Id)
)


CREATE TABLE CustSecondaryAddress
(
    CustomerId INT REFERENCES CustDefaultAddress(CustomerId), 
                   /*No secondary address can be added unless default one exists*/
    AddressId INT REFERENCES [Address](Id),
    PRIMARY KEY(CustomerId, AddressId)
)

If there is an additional requirement that an address must not be present as both a primary and secondary address you can enforce this with a helper table and an indexed view.

CREATE TABLE dbo.TwoRows
  (
     X INT PRIMARY KEY
  );

INSERT INTO dbo.TwoRows
VALUES      (1),
            (2)

GO

CREATE VIEW V
WITH SCHEMABINDING
AS
  SELECT D.AddressId,
         D.CustomerId
  FROM   dbo.CustDefaultAddress D
         JOIN dbo.CustSecondaryAddress S
           ON D.AddressId = S.AddressId
              AND D.CustomerId = S.CustomerId
         CROSS JOIN dbo.TwoRows

GO

CREATE UNIQUE CLUSTERED INDEX IX
  ON V(AddressId, CustomerId)