How to create a unique index in a table so that it only has a record with a specific value in Sqlserver

Question

Hello, I am creating a data structure in Sqlserver and I have the following situation:

• I have a list of contact types (person, public, commercial, etc...)
• This list can be changed by the user but, obligatorily, it can only have 1 element as default.

The table structure looks like this:

create table TipoContato (
    id int not null identity(1,1),
    descricao nvarchar(100) not null,
    padrao bit not null constraint [DF_TipoContato.padrao] default 0
);
create unique index [UX_TipoContato.descricao] 
    on TipoContato(descricao);

Is there any way that I can force SQL to accept only a record as 1 for the default column? The intention is that if it is passed in a INSERT or UPDATE standard as 1, one must force all elements to be 0 beyond the informed.

Note: I have science that I can do this via TRIGGER, but I don’t see how the best possible way to do it... there’s a smarter way to solve the case?

Answer 1

If the SQL Server version is higher than 2008, you can create a filtered index:

create table TipoContato (
    id int not null identity(1,1),
    descricao nvarchar(100) not null,
    padrao bit not null constraint [DF_TipoContato.padrao] default 0
);
create unique index [UX_TipoContato.descricao]  on TipoContato(descricao);
CREATE UNIQUE INDEX IX_padrao_unico ON TipoContato (padrao) WHERE padrao = 1;

If you try to do that:

insert into TipoContato  (descricao,padrao) values ('Test 1',0);
insert into TipoContato  (descricao,padrao) values ('Test 2',1);--padrao true
insert into TipoContato  (descricao,padrao) values ('Test 3',0);
insert into TipoContato  (descricao,padrao) values ('Test 4',0);
insert into TipoContato  (descricao,padrao) values ('Test 5',0);
insert into TipoContato  (descricao,padrao) values ('Test 6',0);
insert into TipoContato  (descricao,padrao) values ('Test 7',1);--erro

the second attempt to insert a true value for the default column will cause an error:

Cannot Insert Duplicate key Row in Object 'dbo.Tipocontato' with Unique index 'Ix_padrao_unico'. The Duplicate key value is (1).

See working on Sqlfiddle.

The idea is to capture the exception and act in some way. The standard column was set by error or was intentional?

Now if you want to always when the default column is set to true the former is amended to false, can’t get away from a Trigger.