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

By: Aaron Bertrand   |   Comments (1)   |   Related: > 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:

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:

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

• Review the following tips and other resources:
  • SQL Server Constraint Unique and Default
  • Should I Use a UNIQUE Constraint or a Unique Index in SQL Server?
  • Difference between SQL Server Unique Indexes and Unique Constraints
  • SQL Server Unique Constraints for Large Text Columns
  • Workaround for lack of support for constraints on SQL Server Memory-Optimized Tables
  • All tips in the Constraints category

About the author

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.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips