Enforcing mutual uniqueness across multiple columns

Question

I'm trying to find an intuitive way of enforcing mutual uniqueness across two columns in a table. I am not looking for composite uniqueness, where duplicate combinations of keys are disallowed; rather, I want a rule where any of the keys cannot appear again in either column. Take the following example: ``` 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), ) ``` A person may pick any room as their living room, and any other room as their dining room. Once a room has been allocated to an occupant, it cannot be allocated again to another person (whether as a living room or as a dining room). I'm aware that this issue can be resolved through data normalization; however, I cannot change the schema make breaking changes to the schema. Update: In response to the proposed answers: Two unique constraints (or two unique indexes) will not prevent duplicates…

Accepted Answer

You could create an "external" constraint in the form of an indexed view: ``` 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); ``` The view is essentially unpivoting the occupied rooms' IDs, putting them all in one column. The unique index on that column makes sure it does not have duplicates. Here are demonstrations of how this method works: failed insert; successful insert. UPDATE As hvd has correctly remarked, the above solution does not catch attempts to insert identical `LivingRoomId` and `DiningRoomId` when they are put on the same row. This is because the `dbo.Rooms` table is matched only once in that case and, therefore, the join produces produces just one row for the pair of references. One way to fix that is suggested in the same comment: additionally to the indexed view, use a CHECK constraint on the `dbo.OccupiedRooms` table to prohibit rows with identical room IDs. The suggested `LivingRoomId <> DiningRoomId` condition, however, will not work for cases where both columns are NULL. To account for that case, the condition could be expanded to this one: ``` LivingRoomId <> DinindRoomId AND (LivingRoomId IS NOT NULL OR DinindRoomId IS NOT NULL) ``` Alternatively, you could change the view's SELECT statement to catch all situations. If `LivingRoomId` and `DinindRoomId` were `NOT NULL` columns, you could avoid a join to `dbo.Rooms` and unpivot the columns using a cross-join to a virtual 2-row table: ``` 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) ``` However, as those columns allow NULLs, this method would not allow you to insert more than one single-reference row. To make it work in your case, you would need to filter out NULL entries, but only if they come from rows where the other reference is not NULL. I believe adding the following WHERE clause to the above query would suffice: ``` 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 ```

Enforcing mutual uniqueness across multiple columns

Solution

Problem