sql - tabla - restricciones de nombres que puede tener una base de datos.

Restricción de SQL Validar valores únicos

Question

Tengo una tabla y necesito restringir los datos insertados, ya uso un activador pero me preguntaba si podría hacer la misma tarea con una restricción

Los campos son:

Id
Date
Passport
...
Deleted

La restricción debe permitir n registros mientras el campo Eliminado es 1 , pero si el campo Eliminado es 0, solo debe haber una fila con la misma Fecha y Pasaporte.

Debería trabajar para estos pasos:

1. Adición de una fila con Id = 1, Fecha = 2018-05-01, Pasaporte = MPEUIE80, Eliminado = 0
2. Eliminar la fila con Id = 1, por lo que el campo Eliminado será 1
3. Adición de una fila con Id = 2, Fecha = 2018-05-01, Pasaporte = MPEUIE80, Eliminado = 0
4. Eliminar la fila con Id = 2, por lo que el campo Eliminado será 1
5. Adición de una fila con Id = 3, Fecha = 2018-05-01, Pasaporte = MPEUIE80, Eliminado = 0
6. Adición de una fila con Id = 4, Fecha = 2018-05-01, Pasaporte = MPEUIE80, Eliminado = 0

Hasta el quinto paso, todo funciona, pero en el último paso debe haber un error, porque no puedo manejar dos filas con la misma fecha , el mismo pasaporte y ambos con Deleted = 0

Gracias por adelantado.

Answer 1

Puede utilizar un índice único y filtrado:

create table [t](
[id] [int] NULL,
[date] [datetime] NULL,
[passport] [char](8) NULL,
[deleted] [int] NULL
)
create unique index unq_t_date_passport on t(date, passport)
where deleted = 0;

EDITAR:

Si los índices filtrados no funcionan en su versión de SQL Server, es posible que desee verificar el nivel de compatibilidad . Debe estar disponible siempre que la compatibilidad se establezca en 100 o superior. Puede verificar la versión de su servidor y el nivel de compatibilidad usando:

SELECT SERVERPROPERTY(''ProductVersion'');
SELECT name, compatibility_level FROM sys.databases;

Existe otro método que funcionará a partir de SQL Server 2005, asumiendo que el ID es único y no negativo. Utiliza una columna calculada para hacer esencialmente lo mismo que el índice filtrado:

alter table t
add column deleted_id (case when deleted = 0 then -1 else id end) persisted;
create unique index unq_t_passport_date_deleted_id on t(passport, date, deleted_id);