constraints - SQL Server: Unique Composite Key that looks order

Question

I created a small test table with 2 integer columns pkId1 and pkId2. I set the primary key as a composite that includes both columns, (pkId1 is first, then pkId2).

Now I inserted some values, in the following order: (1,1), (1,2), (2,1) and (2,2). I was expecting (2,1) to fail, but it did not. How can I force it to fail ?

I could check it into my application, but I want to do it in SQL side. Also, I thought about using a stored procedure, but I would like to know if there a setting to set for the composite key in the designer of the table.

Edit

Order of pkId1 and pkId2 do matter. For example, if (2,1) was already in the table, (1,2) should not be accepted. The reason for this is because the row represents a link between two entities. Thus, it should be read there is a link from 2 to 1 for (2,1) and a link from 1 to 2 for (1,2).

As for same value in both columns (1,1), (2,2), etc., they are accepted, because they are a special case.

question from:https://stackoverflow.com/questions/65601738/sql-server-unique-composite-key-that-looks-order

Answer 1

One option is create an PERSISTED column

Example

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TestTable](
    [pkId1] [int] NOT NULL,
    [pkId2] [int] NOT NULL,
    [pkuc]  AS (case when [pkId1]<[pkId2] 
                     then concat([pkId1],'-',[pkId2]) 
                     else concat([pkId2],'-',[pkId1]) 
                end) PERSISTED NOT NULL UNIQUE
) ON [PRIMARY]
GO

Test

Insert Into [dbo].[TestTable] (pkId1,pkId2) values (1,2)
Insert Into [dbo].[TestTable] (pkId1,pkId2) values (2,1)

Select * from [dbo].[TestTable]

Updated Table

pkId1   pkId2   pkuc
1       2       1-2