sql - varias - restricción condicional única

where con varias condiciones mysql

Question

Tengo una situación en la que necesito imponer una restricción única en un conjunto de columnas, pero solo para un valor de una columna.

Entonces, por ejemplo, tengo una tabla como Table (ID, Name, RecordStatus).

RecordStatus solo puede tener un valor 1 o 2 (activo o eliminado), y quiero crear una restricción única en (ID, RecordStatus) solo cuando RecordStatus = 1, ya que no me importa si hay múltiples registros eliminados con el mismo CARNÉ DE IDENTIDAD.

Además de escribir disparadores, ¿puedo hacer eso?

Estoy usando SQL Server 2005.

Answer 1

Agregue una restricción de verificación como esta. La diferencia es que devolverá falso si Status = 1 y Count> 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, ''No Problems'', 2);
INSERT INTO CheckConstraint VALUES (1, ''No Problems'', 2);
INSERT INTO CheckConstraint VALUES (1, ''No Problems'', 2);
INSERT INTO CheckConstraint VALUES (1, ''No Problems'', 1);

INSERT INTO CheckConstraint VALUES (2, ''Oh no!'', 1);
INSERT INTO CheckConstraint VALUES (2, ''Oh no!'', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, ''Oh no!'', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

Answer 2

Como va a permitir duplicados, una restricción única no funcionará. Puede crear una restricción de comprobación para la columna RecordStatus y un procedimiento almacenado para INSERT que verifique los registros activos existentes antes de insertar identificadores duplicados.

Answer 3

Mira, el índice filtrado . De la documentación (énfasis mío):

Un índice filtrado es un índice optimizado no agrupado especialmente adecuado para cubrir consultas que seleccionan de un subconjunto de datos bien definido. Utiliza un predicado de filtro para indexar una parte de las filas en la tabla. Un índice filtrado bien diseñado puede mejorar el rendimiento de la consulta y reducir el mantenimiento del índice y los costos de almacenamiento en comparación con los índices de tabla completa.

Y aquí hay un ejemplo que combina un índice único con un predicado de filtro:

create unique index [MyIndex]
on [MyTable]([ID])
where [RecordStatus] = 1

Esto esencialmente impone la singularidad de ID cuando RecordStatus es 1 .

_{Nota: el índice filtrado se introdujo en SQL Server 2008. Para las versiones anteriores de SQL Server, vea esta respuesta .}

Answer 4

Puede mover los registros eliminados a una tabla que carece de la restricción, y quizás use una vista con UNION de las dos tablas para preservar la apariencia de una sola tabla.

Answer 5

Puedes hacer esto de una manera realmente hacky ...

Cree una vista esquemática en su tabla.

CREAR VIEW Sea cual sea SELECT * FROM Table WHERE RecordStatus = 1

Ahora crea una restricción única en la vista con los campos que quieras.

Sin embargo, una nota sobre las vistas esquemáticas, si cambia las tablas subyacentes tendrá que volver a crear la vista. Un montón de trampas por eso.

Answer 6

Si no puede usar NULL como RecordStatus como lo sugirió Bill, podría combinar su idea con un índice basado en funciones. Cree una función que devuelva NULL si RecordStatus no es uno de los valores que desea considerar en su restricción (y RecordStatus en caso contrario) y cree un índice sobre eso.

Eso tendrá la ventaja de que no tiene que examinar explícitamente otras filas en la tabla en su restricción, lo que podría causarle problemas de rendimiento.

Debo decir que no conozco SQL Server en absoluto, pero he utilizado con éxito este enfoque en Oracle.