Обеспечение взаимной уникальности в нескольких столбцах

Я пытаюсь найти интуитивный способ обеспечения взаимной уникальности между двумя столбцами в таблице. Я не ищу сложную уникальность, где дублирующиеся комбинации ключей запрещены; скорее, я хочу правило, когда любой из ключей не может появляться снова в любом столбце. Возьмем следующий пример:

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),
)

Человек может выбрать любую комнату, как свою гостиную, и любую другую комнату в качестве столовой. Когда комната была выделена жильцу, она не может быть выделена другому человеку (будь то в гостиной или в столовой).

Я знаю, что эта проблема может быть решена с помощью нормализации данных; однако я не могу изменить схему внести изменения в схему.

Обновление. В ответ на предлагаемые ответы:

Два уникальных ограничения (или два уникальных индекса) не будут препятствовать дублированию в двух столбцах. Точно так же простое ограничение проверки LivingRoomId != DiningRoomId не будет препятствовать дублированию строк. Например, я хочу, чтобы следующие данные были запрещены:

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

Комната 2 занята одновременно Алексом (как гостиная) и Линкольном (как столовая); это не должно быть разрешено.

Обновление 2. Я провел несколько тестов по трем основным предлагаемым решениям, указав, сколько времени потребуется, чтобы вставить 500 000 строк в таблицу Occupants причем каждая строка имеет пару случайных уникальных идентификаторов номеров.

Расширение таблицы Occupants с уникальными индексами и контрольное ограничение (которое вызывает скалярную функцию) приводит к тому, что вставка занимает примерно в три раза больше. Реализация скалярной функции является неполной, и только проверка того, что гостиная нового жителя не противоречит существующей столовой обитателей. Я не смог получить вставку в разумные сроки, если была выполнена обратная проверка.

Добавление триггера, который вставляет каждую комнату для пассажиров в новую строку в другую таблицу, снижает производительность на 48%. Точно так же индексированное представление увеличивается на 43%. На мой взгляд, использование индексированного представления является более чистым, поскольку оно позволяет избежать необходимости создания другой таблицы, а также позволяет SQL Server автоматически обрабатывать обновления и удалять также.

Полные сценарии и результаты тестов приведены ниже:

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)

*/

Ответы

Ответ 1

Вы можете создать "внешнее" ограничение в виде индексированного представления:

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);

Вид по существу не содержит идентификаторов занятых комнат, помещая их в одну колонку. Уникальный индекс в этом столбце гарантирует, что он не имеет дубликатов.

Вот демонстрации того, как работает этот метод:

UPDATE

Как hvd правильно заметил, вышеупомянутое решение не пытается попытаться вставить идентичные LivingRoomId и DiningRoomId, когда они помещаются в одну строку. Это связано с тем, что таблица dbo.Rooms сопоставляется только один раз в этом случае и, следовательно, объединение создает только одну строку для пары ссылок.

Один из способов исправления, предложенный в том же комментарии: дополнительно к индексированному представлению используйте ограничение CHECK в таблице dbo.OccupiedRooms, чтобы запретить строки с идентичными идентификаторами комнат. Однако предлагаемое условие LivingRoomId <> DiningRoomId не будет работать для случаев, когда оба столбца имеют значение NULL. Для учета этого случая условие можно было бы расширить до этого:

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

В качестве альтернативы вы можете изменить оператор SELECT представления, чтобы поймать все ситуации. Если LivingRoomId и DinindRoomId были столбцами NOT NULL, вы могли бы избежать присоединения к dbo.Rooms и отключить столбцы, используя перекрестное соединение с виртуальной таблицей из двух строк:

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)

Однако, поскольку эти столбцы допускают NULL, этот метод не позволит вам вставлять несколько строк с одной ссылкой. Чтобы он работал в вашем случае, вам нужно отфильтровать записи NULL, но только если они поступают из строк, где другая ссылка не является NULL. Я считаю, что добавление следующего предложения WHERE к вышеуказанному запросу будет достаточным:

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

Ответ 2

Я думаю, что единственный способ сделать это - использовать ограничение и функцию.

Псевдокод (не делал это в течение длительного времени):

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

Затем используйте эту функцию в контрольном ограничении....

Альтернативой будет использование промежуточной таблицы OccupiedRoom, где вы всегда будете вставлять в нее комнаты, которые используются (например, триггером?) и FK, вместо таблицы Room

Реакция на комментарий:

Нужно ли принудительно применять его непосредственно в таблице или это нарушение ограничений происходит в ответ на вставку/обновление? Потому что тогда я так думаю:

  • создать простую таблицу:

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

  • создать триггер для вставки/обновления/удаления, который гарантирует, что любая Комната, используемая в Жильцах, также хранится в RoomID.

  • Если вы попытаетесь дублировать использование комнаты, таблица RoomTaken вызовет нарушение PK

Не уверен, что этого достаточно и/или как он будет сравнивать скорость с UDF (я предполагаю, что это будет лучше).

И да, я вижу проблему, что RoomTaken не будет использовать FK для использования в оккупантах, но... действительно, вы работаете под некоторыми ограничениями, и нет идеального решения - это скорость (UDF) и 100% принудительное исполнение по-моему.

Ответ 3

Вы добавили контрольное ограничение в таблицу Occupants:

CHECK (LivingRoomId <> DiningRoomId)

Если вы также хотите обрабатывать NULL:

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

Ответ 4

Вы можете выполнить это с помощью двух уникальных ограничений. Если вы хотите разрешить более одного NULL, используйте отфильтрованные индексы, каждый с WHERE... NOT NULL.