How to constrain many-to-many to mark a single record as a "default"?

Question

Suppose I have the following entities:

customer(id, name)

template(id, name)

With a junction table:

customer_template(id, customerId, templateId, is_default)

A customer may have any number of templates but only one is allowed to be the default.

How do I constrain the junction table so that there is one and only one default template assigned?

Answer 1

You could use a filtered unique index.

create unique nonclustered index ixf_customer_template_default 
  on dbo.customer_template(customerId,is_default)
    where is_default = 1;

That will allow only one default and any number of non default rows per customer.

rextester demo: http://rextester.com/HLQB10886

create table customer_template(
    id int identity(1,1)
  , customerId int
  , templateId int
  , is_default bit
);

create unique nonclustered index ixf_customer_template_default 
  on dbo.customer_template(customerId,is_default)
    where is_default = 1;

insert into customer_template values
 (1,1,0),(1,2,0),(1,3,1);

/* error when adding another default */

insert into customer_template values
 (1,4,1) 

Cannot insert duplicate key row in object 'dbo.customer_template' with unique index 'ixf_customer_template_default'. The duplicate key value is (1, 1). The statement has been terminated.