sql server - Ensure that one and only one default is defined for a set -

i have customer table has 1 many relationship address table. want constrain database customer with addresses have 1 (and one) default address.

i can quite add constraint ensure there ever 1 default address each customer. struggling on how apply constraint ensures address marked default.

to summarize:

• a customer not required have addresses.
• if customer has addresses there must default address.
• there must 1 default address per customer.

here example of problem , 'unit' tests. using link table join customers , 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 customer values (1, 'mr greedy')  insert [address] values (1, 'roly-poly house, fatland') insert [address] values (2, 'giant cottage, cave')  -- should succeed insert custaddress values (1, 1, 1) insert custaddress values (1, 2, 0)  delete custaddress  -- should fail no default address set insert custaddress values (1, 1, 0)  delete custaddress  -- should fail end no defualt address set insert custaddress values (1, 1, 1) insert custaddress values (1, 2, 0) update custaddress set [default] = 0 customerid = 1 , addressid = 1  delete custaddress  -- should fail end no defualt address set insert custaddress values (1, 1, 1) insert custaddress values (1, 2, 0) delete custaddress customerid = 1 , addressid = 1 

how changing 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 1 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 added unless default 1 exists*/     addressid int references [address](id),     primary key(customerid, addressid) ) 

if there additional requirement address must not present both primary , secondary address can enforce helper table , indexed view.

create table dbo.tworows   (      x int primary key   );  insert dbo.tworows values      (1),             (2)  go  create view v schemabinding   select d.addressid,          d.customerid     dbo.custdefaultaddress d          join dbo.custsecondaryaddress s            on d.addressid = s.addressid               , d.customerid = s.customerid          cross join dbo.tworows  go  create unique clustered index ix   on v(addressid, customerid)