sql - una - Hacer cumplir la unicidad mutua en varias columnas

tipos de restricciones en base de datos

Question

Estoy tratando de encontrar una manera intuitiva de imponer la singularidad mutua en dos columnas de una tabla. No estoy buscando unicidad compuesta, donde no se permiten combinaciones duplicadas de claves; más bien, quiero una regla donde cualquiera de las claves no pueda volver a aparecer en ninguna de las columnas. Tomemos el siguiente ejemplo:

CREATE TABLE Rooms
(
    Id INT NOT NULL PRIMARY KEY,
)

CREATE TABLE Occupants
(
    PersonName VARCHAR(20),
    LivingRoomId INT NULL REFERENCES Rooms (Id),
    DiningRoomId INT NULL REFERENCES Rooms (Id),
)

Una persona puede elegir cualquier habitación como sala de estar y cualquier otra habitación como comedor. Una vez que una habitación ha sido asignada a un ocupante, no se puede volver a asignar a otra persona (ya sea como sala de estar o comedor).

Soy consciente de que este problema se puede resolver a través de la normalización de datos; sin embargo, no puedo cambiar el esquema para hacer cambios en el esquema.

Actualización : En respuesta a las respuestas propuestas:

Dos restricciones únicas (o dos índices únicos) no evitarán duplicados en las dos columnas. De manera similar, una simple restricción de comprobación LivingRoomId != DiningRoomId no evitará duplicados en las filas . Por ejemplo, quiero que los siguientes datos estén prohibidos:

INSERT INTO Rooms VALUES (1), (2), (3), (4)
INSERT INTO Occupants VALUES (''Alex'',    1, 2)
INSERT INTO Occupants VALUES (''Lincoln'', 2, 3)

La habitación 2 está ocupada simultáneamente por Alex (como sala de estar) y por Lincoln (como sala de comedor); Esto no debería estar permitido.

Actualización ₂ : He realizado algunas pruebas en las tres soluciones principales propuestas, cronometrando el tiempo que tardarían en insertar 500,000 filas en la tabla de Occupants , y cada fila tendrá un par de identificadores de habitación únicos al azar.

La extensión de la tabla de Occupants con índices únicos y una restricción de verificación (que llama a una función escalar) hace que la inserción dure aproximadamente tres veces más. La implementación de la función escalar es incompleta, solo se verifica que la sala de estar de los nuevos ocupantes no esté en conflicto con el comedor de los ocupantes existentes. No pude hacer que el inserto se complete en un tiempo razonable si también se realizó la verificación inversa.

Agregar un disparador que inserta la habitación de cada ocupante como una nueva fila en otra tabla reduce el rendimiento en un 48%. Del mismo modo, una vista indexada tarda un 43% más. En mi opinión, el uso de una vista indizada es más limpio, ya que evita la necesidad de crear otra tabla, y permite que SQL Server también maneje las actualizaciones y eliminaciones automáticamente.

Los guiones completos y los resultados de las pruebas se dan a continuación:

SET STATISTICS TIME OFF
SET NOCOUNT ON

CREATE TABLE Rooms
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    RoomName VARCHAR(10),
)

CREATE TABLE Occupants
(
    Id INT NOT NULL PRIMARY KEY IDENTITY(1,1),
    PersonName VARCHAR(10),
    LivingRoomId INT NOT NULL REFERENCES Rooms (Id),
    DiningRoomId INT NOT NULL REFERENCES Rooms (Id)
)

GO

DECLARE @Iterator INT = 0
WHILE (@Iterator < 10)
BEGIN
    INSERT INTO Rooms
    SELECT TOP (1000000) ''ABC''
    FROM sys.all_objects s1 WITH (NOLOCK)
        CROSS JOIN sys.all_objects s2 WITH (NOLOCK)
        CROSS JOIN sys.all_objects s3 WITH (NOLOCK);
    SET @Iterator = @Iterator + 1
END;

DECLARE @RoomsCount INT = (SELECT COUNT(*) FROM Rooms);

SELECT TOP 1000000 RoomId
INTO ##RandomRooms
FROM 
(
    SELECT DISTINCT
        CAST(RAND(CHECKSUM(NEWID())) * @RoomsCount AS INT) + 1 AS RoomId
    FROM sys.all_objects s1 WITH (NOLOCK)
        CROSS JOIN sys.all_objects s2 WITH (NOLOCK)

) s

ALTER TABLE ##RandomRooms
ADD Id INT IDENTITY(1,1)

SELECT
    ''XYZ'' AS PersonName,
    R1.RoomId AS LivingRoomId,
    R2.RoomId AS DiningRoomId
INTO ##RandomOccupants
FROM ##RandomRooms R1
    JOIN ##RandomRooms R2
        ON  R2.Id % 2 = 0
        AND R2.Id = R1.Id + 1

GO

PRINT CHAR(10) + ''Test 1: No integrity check''

CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT OFF
SET STATISTICS TIME ON

INSERT INTO Occupants
SELECT *
FROM ##RandomOccupants

SET STATISTICS TIME OFF
SET NOCOUNT ON

TRUNCATE TABLE Occupants

PRINT CHAR(10) + ''Test 2: Unique indexes and check constraint''

CREATE UNIQUE INDEX UQ_LivingRoomId
ON Occupants (LivingRoomId)

CREATE UNIQUE INDEX UQ_DiningRoomId
ON Occupants (DiningRoomId)

GO

CREATE FUNCTION CheckExclusiveRoom(@occupantId INT)
RETURNS BIT AS
BEGIN
RETURN 
(
    SELECT CASE WHEN EXISTS
    (
        SELECT *
        FROM Occupants O1
            JOIN Occupants O2
                ON O1.LivingRoomId = O2.DiningRoomId
             -- OR O1.DiningRoomId = O2.LivingRoomId
        WHERE O1.Id = @occupantId
    )
    THEN 0
    ELSE 1
    END
)
END

GO

ALTER TABLE Occupants
ADD CONSTRAINT ExclusiveRoom 
CHECK (dbo.CheckExclusiveRoom(Id) = 1)

CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT OFF
SET STATISTICS TIME ON

INSERT INTO Occupants
SELECT *
FROM ##RandomOccupants

SET STATISTICS TIME OFF
SET NOCOUNT ON

ALTER TABLE Occupants DROP CONSTRAINT ExclusiveRoom
DROP INDEX UQ_LivingRoomId ON Occupants
DROP INDEX UQ_DiningRoomId ON Occupants
DROP FUNCTION CheckExclusiveRoom

TRUNCATE TABLE Occupants

PRINT CHAR(10) + ''Test 3: Insert trigger''

CREATE TABLE RoomTaken 
(
    RoomId INT NOT NULL PRIMARY KEY REFERENCES Rooms (Id) 
)

GO

CREATE TRIGGER UpdateRoomTaken
ON Occupants
AFTER INSERT
AS 
    INSERT INTO RoomTaken
    SELECT RoomId
    FROM
    (
        SELECT LivingRoomId AS RoomId
        FROM INSERTED
            UNION ALL
        SELECT DiningRoomId AS RoomId
        FROM INSERTED
    ) s

GO  

CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT OFF
SET STATISTICS TIME ON

INSERT INTO Occupants
SELECT *
FROM ##RandomOccupants

SET STATISTICS TIME OFF
SET NOCOUNT ON

DROP TRIGGER UpdateRoomTaken
DROP TABLE RoomTaken

TRUNCATE TABLE Occupants

PRINT CHAR(10) + ''Test 4: Indexed view with unique index''

CREATE TABLE TwoRows
(
    Id INT NOT NULL PRIMARY KEY
)

INSERT INTO TwoRows VALUES (1), (2)

GO

CREATE VIEW OccupiedRooms
WITH SCHEMABINDING
AS
    SELECT RoomId = CASE R.Id WHEN 1 
                    THEN O.LivingRoomId 
                    ELSE O.DiningRoomId 
                    END
    FROM dbo.Occupants O
        CROSS JOIN dbo.TwoRows R

GO

CREATE UNIQUE CLUSTERED INDEX UQ_OccupiedRooms
ON OccupiedRooms (RoomId);

CHECKPOINT;
DBCC FREEPROCCACHE WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
SET NOCOUNT OFF
SET STATISTICS TIME ON

INSERT INTO Occupants
SELECT *
FROM ##RandomOccupants

SET STATISTICS TIME OFF
SET NOCOUNT ON

DROP INDEX UQ_OccupiedRooms ON OccupiedRooms
DROP VIEW OccupiedRooms
DROP TABLE TwoRows

TRUNCATE TABLE Occupants

DROP TABLE ##RandomRooms
DROP TABLE ##RandomOccupants

DROP TABLE Occupants
DROP TABLE Rooms


/* Results:

Test 1: No integrity check

 SQL Server Execution Times:
   CPU time = 5210 ms,  elapsed time = 10853 ms.

(500000 row(s) affected)

Test 2: Unique indexes and check constraint

 SQL Server Execution Times:
   CPU time = 21996 ms,  elapsed time = 27019 ms.

(500000 row(s) affected)

Test 3: Insert trigger
SQL Server parse and compile time: 
   CPU time = 5663 ms, elapsed time = 11192 ms.

 SQL Server Execution Times:
   CPU time = 4914 ms,  elapsed time = 4913 ms.

(1000000 row(s) affected)

 SQL Server Execution Times:
   CPU time = 10577 ms,  elapsed time = 16105 ms.

(500000 row(s) affected)

Test 4: Indexed view with unique index

 SQL Server Execution Times:
   CPU time = 10171 ms,  elapsed time = 15777 ms.

(500000 row(s) affected)

*/

Answer 1

Agregaría una restricción de verificación a la tabla Occupants :

CHECK (LivingRoomId <> DiningRoomId)

Si quieres manejar NULL también:

CHECK ((LivingRoomId <> DiningRoomId) or LivingRoomId is NULL or DiningRoomId is NULL)

Answer 2

Creo que la única manera de hacer esto es usar la restricción y una función.

Pseudo código (no he hecho esto por mucho tiempo):

CREATE FUNCTION CheckExlusiveRoom
RETURNS bit
declare @retval bit
set @retval = 0
    select retval = 1 
      from Occupants as Primary
      join Occupants as Secondary
        on Primary.LivingRoomId = Secondary.DiningRoomId
     where Primary.ID <> Secondary.ID
        or (   Primary.DiningRoomId= Secondary.DiningRoomId
            or Primary.LivingRoomId = Secondary.LivingRoomID)
return @retval
GO

Luego, usa esta función en una restricción de verificación ....

Alternativo sería usar una tabla intermedia OccupiedRoom, donde siempre se insertarán las salas que se usan (¿por ejemplo, disparador?) Y FK en lugar de la tabla de sala

Reacción al comentario:

¿Necesita imponerlo directamente en la tabla, o es una infracción de restricción ocurriendo en reacción a la inserción / actualización suficiente? Porque entonces estoy pensando así:

1. crear una tabla simple:

   create table RoomTaken (RoomID int primary key references Room (Id) )

2. cree un activador al insertar / actualizar / eliminar, que asegure que cualquier Habitación utilizada en Ocupantes se mantenga también en RoomID.

3. Si intenta duplicar el uso de la sala, la tabla RoomTaken arrojará una infracción PK

No estoy seguro de si esto es suficiente y / o cómo se compararía en velocidad con el UDF (supongo que sería superior).

Y sí, veo el problema de que RoomTaken no aceptaría el uso en Ocupantes, pero ... en realidad, usted está trabajando bajo ciertas restricciones y no hay una solución perfecta: su velocidad (UDF) vs 100% de integridad en mi opinión .

Answer 3

Podría crear una restricción "externa" en forma de vista indizada:

CREATE VIEW dbo.OccupiedRooms
WITH SCHEMABINDING
AS
SELECT r.Id
FROM   dbo.Occupants AS o
INNER JOIN dbo.Rooms AS r ON r.Id IN (o.LivingRoomId, o.DiningRoomId)
;
GO

CREATE UNIQUE CLUSTERED INDEX UQ_1 ON dbo.OccupiedRooms (Id);

La vista es, en esencia, univivando las ID de las habitaciones ocupadas, colocándolas todas en una columna. El índice único en esa columna se asegura de que no tenga duplicados.

Aquí están las demostraciones de cómo funciona este método:

• inserto fallado

• inserción exitosa .

ACTUALIZAR

Como hvd ha señalado correctamente , la solución anterior no LivingRoomId intentos de insertar LivingRoomId y DiningRoomId idénticos cuando se colocan en la misma fila. Esto se debe a que la tabla dbo.Rooms coincide solo una vez en ese caso y, por lo tanto, la unión produce solo una fila para el par de referencias.

Una forma de solucionarlo se sugiere en el mismo comentario: además de la vista indizada, use una restricción CHECK en la tabla dbo.OccupiedRooms para prohibir filas con ID de habitación idénticas. Sin embargo, la condición LivingRoomId <> DiningRoomId sugerida no funcionará en los casos en que ambas columnas sean NULL. Para dar cuenta de ese caso, la condición podría ampliarse a este:

LivingRoomId <> DinindRoomId AND (LivingRoomId IS NOT NULL OR DinindRoomId IS NOT NULL)

Alternativamente, puede cambiar la instrucción SELECT de la vista para capturar todas las situaciones. Si LivingRoomId y DinindRoomId NOT NULL fueran columnas NOT NULL , podría evitar una unión a dbo.Rooms y anular la división de las columnas mediante una combinación cruzada a una tabla virtual de 2 filas:

SELECT  Id = CASE x.r WHEN 1 THEN o.LivingRoomId ELSE o.DiningRoomId END
FROM    dbo.Occupants AS o
CROSS
JOIN    (SELECT 1 UNION ALL SELECT 2) AS x (r)

Sin embargo, como esas columnas permiten NULL, este método no le permitiría insertar más de una fila de referencia única. Para que funcione en su caso, tendría que filtrar las entradas NULL, pero solo si provienen de filas donde la otra referencia no es NULL. Creo que sería suficiente agregar la siguiente cláusula WHERE a la consulta anterior:

WHERE o.LivingRoomId IS NULL AND o.DinindRoomId IS NULL
   OR x.r = 1 AND o.LivingRoomId IS NOT NULL
   OR x.r = 2 AND o.DinindRoomId IS NOT NULL

Answer 4

Puedes lograr esto con 2 restricciones únicas. Si desea permitir más de un NULL, use índices filtrados, cada uno con WHERE ... NOT NULL.