Enforce a Unique Constraint Where Order Does Not Matter in SQL Server


By:   |   Updated: 2021-02-19   |   Comments (1)   |   Related: More > Constraints


Problem

A problem that comes up occasionally with constraints in SQL Server is when a unique constraint applies to multiple columns, but the values in those columns can be populated in any order. For example, if a table holds interactions between two users, and has columns User1 and User2, there should only be one row allowed for users 100 and 200, regardless of whether the data is entered as 100, 200 or 200, 100.

Solution

Let's build a test case and show two ways how this unique constraint can be put in place to handle this situation where we want to store two values in two columns only once regardless of what value goes into each column. So as the example stated, we have columns User1 and User2 and values 100 and 200 can only be stored once regardless if the data is entered as 100, 200 or 200, 100.

Example

Consider the following table, which holds a row for each conversation between two users:

CREATE TABLE dbo.Conversations
(
ConversationID int IDENTITY(1,1) NOT NULL,
User1 int NOT NULL, -- FOREIGN KEY REFERENCES dbo.Users(UserID)...
User2 int NOT NULL, -- FOREIGN KEY REFERENCES dbo.Users(UserID)...
CONSTRAINT PK_Conversations PRIMARY KEY(ConversationID),
CONSTRAINT UQ_Conversations_Users UNIQUE(User1, User2)
);

When user 100 starts a conversation with user 200, we insert a row into the table:

INSERT dbo.Conversations(User1, User2) VALUES(100, 200);

If user 200 tries to start a different conversation with user 100, we want to prevent this from happening at the table level. But our constraint doesn't solve that because, as far as the constraint is concerned, order matters. The constraint above is based on the uniqueness of User1 and User2 and in both cases that would be true.

If the application allows this attempt, the following insert will succeed, because the order of the values is still unique.

INSERT dbo.Conversations(User1, User2) VALUES(200, 100);

We can't simply add a second constraint with the columns in the reverse order, because both inserts would still succeed.

We Need to be More Creative

Let's start by assuming a user can't start a conversation with themselves.

We should define this explicitly using the following constraint:

ALTER TABLE dbo.Conversations ADD CONSTRAINT CK_ConvoNotWithSelf CHECK (User1 <> User2);

With that constraint in place, we can then consider the two columns in a way that order doesn't matter, but without changing any of the logic that inserts the row, and without having to worry about the case where the values are the same.

Build SQL Server Constraint with Computed Columns

One way to do this is to create two non-persisted computed columns, and assign their values in UserID order, always putting the lesser value in the first column:

ALTER TABLE dbo.Conversations ADD LesserUser AS 
(CASE WHEN User1 < User2 THEN User1 ELSE User2 END); ALTER TABLE dbo.Conversations ADD GreaterUser AS
(CASE WHEN User1 > User2 THEN User1 ELSE User2 END);

Now we can visualize how we might employ a unique constraint:

SELECT * FROM dbo.Conversations;

Results:

ConversationID  User1  User2  LesserUser  GreaterUser
-------------- ----- ----- ---------- -----------
1 100 200 100 200
2 200 100 100 200

Note that no matter which order values were added to User1 and User2, the computed columns LesserUser and GreaterUser show them in the same order.

Now, we can apply a single constraint on these two columns (but first we'll have to fix existing, non-compliant data, and drop the original constraint):

ALTER TABLE dbo.Conversations DROP CONSTRAINT UQ_Conversations_Users;
GO
DELETE dbo.Conversations;
GO ALTER TABLE dbo.Conversations
ADD CONSTRAINT UQ_Conversations_UsersOrdered UNIQUE(LesserUser, GreaterUser);

Now if we execute the two inserts, only the first INSERT succeeds:

INSERT dbo.Conversations(User1, User2) VALUES(100, 200);
GO INSERT dbo.Conversations(User1, User2) VALUES(200, 100);

And the second INSERT fails with this message:

Msg 2627, Level 14, State 1, Line 54
Violation of UNIQUE KEY constraint 'UQ_Conversations_UsersOrdered'. Cannot insert duplicate key in object 'dbo.Conversations'. The duplicate key value is (100, 200).

This is what we wanted!

Build SQL Server Constraint with a Trigger

If schema changes are problematic, you can accomplish something similar with a trigger:

CREATE TRIGGER dbo.ReorderUsersForConversations
ON dbo.Conversations
FOR INSERT, UPDATE
AS
BEGIN UPDATE c SET c.User1 = i.User2,
c.User2 = i.User1
FROM dbo.Conversations AS c
INNER JOIN inserted AS i
ON c.ConversationID = i.ConversationID
WHERE i.User1 > i.User2; END
GO

Basically, if User1 is greater than User2, the values will be swapped.

Let's try it out! First we can clean out the data, drop the ordered constraint and the computed columns, and re-create the original constraint:

DELETE dbo.Conversations;


ALTER TABLE dbo.Conversations DROP CONSTRAINT UQ_Conversations_UsersOrdered;
ALTER TABLE dbo.Conversations DROP COLUMN LesserUser, GreaterUser;
ALTER TABLE dbo.Conversations ADD CONSTRAINT UQ_Conversations_Users UNIQUE(User1, User2);

Now we can try our two INSERTs again:

INSERT dbo.Conversations(User1, User2) VALUES(100, 200);
GO INSERT dbo.Conversations(User1, User2) VALUES(200, 100);

As with the computed columns solution, the second insert fails when the values are swapped:

Msg 2627, Level 14, State 1, Procedure ReorderUsersForConversations, Line 6
Violation of UNIQUE KEY constraint 'UQ_Conversations_Users'. Cannot insert duplicate key in object 'dbo.Conversations'. The duplicate key value is (100, 200).

This is also the result we were after!

Other Possibilities

If order truly doesn't matter, it would be easier to change the logic that inserts the data to make sure it inserts the lesser UserID into the first column, always. In this case, the original constraint would have been sufficient. But it's often difficult to add such logic to the application, never mind rely on all current and future queries to persist that logic.

Another approach might be to model the data differently: a conversation has a row per user. This introduces a different problem – constraining two users to a single conversation – which can't be handled with a row-based constraint. But it removes the complexity of caring about order, and also opens up the possibility of conversations involving more than two users (without having to change the schema).

Conclusion

You can solve this "order doesn't matter" issue in multiple ways. I don't have a strong personal preference between the two approaches I've illustrated here – I like that the computed columns are more discoverable than the trigger, but I also like that the trigger avoids schema changes and, more precisely, redundant columns. What works best for you will depend on your existing conventions and deployment processes.

Next Steps





get scripts

next tip button



About the author
MSSQLTips author Aaron Bertrand Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.

View all my tips


Article Last Updated: 2021-02-19

Comments For This Article




Thursday, July 22, 2021 - 5:02:23 PM - Jacco Schalkwijk Back To Top (89038)
You can also use an indexed view, which is functionally equivalent to adding the computed columns to the table, but has the advantage of not making schema changes:

create view dbo.chk_Conversations
with schemabinding
as
select CASE WHEN User1 < User2 THEN User1 ELSE User2 end as LesserUser
, CASE WHEN User1 > User2 THEN User1 ELSE User2 end as GreaterUser
from dbo.Conversations;
go

create unique clustered index ixu_chk_Conversations
on dbo.chk_Conversations (LesserUser, GreaterUser);


download














get free sql tips
agree to terms