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 Custom…

Accepted Answer

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

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

Solution

Problem