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

Question

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

Navigation

#1 by (2 votes)

1

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

I have a list of contact types (people, public, commercial, etc ...)
This list can be changed by the user, but 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 I can force SQL to only accept one record as 1 for the default column? The intent is that, if passed in a INSERT or UPDATE the default as 1, one must force all elements to be 0 beyond that reported.

Note: I am aware that I can do this via TRIGGER , but I do not see it as the best way to do it ... Is there a smarter way to solve the case?

sql sql-server

asked by anonymous 30.07.2018 / 16:18

1 answer

How to update Java application of .war format for several clients? How to list all names of Access tables?

score 2 · Accepted Answer

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 this:

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 enter a value true to the default column will cause an error:

Can not insert duplicate key row in object 'dbo.TipoContact' with unique index 'IX_padrao_unico'. The duplicate key value is (1).

See working in SQLFiddle .

The idea is to capture the exception and act in some way. Has the default column been set by mistake or was it intentional?

Now if you want the default column always to true to be changed to false , you can not escape a trigger strong>.