Я пытаюсь найти интуитивный способ обеспечения взаимной уникальности между двумя столбцами в таблице. Я не ищу сложную уникальность, где дублирующиеся комбинации ключей запрещены; скорее, я хочу правило, когда любой из ключей не может появляться снова в любом столбце. Возьмем следующий пример:
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)
*/