CODEWITHSUNDEEP
sql-serverconstraintscomposite-primary-key
RegularNormalDayGuy
RegularNormalDayGuy

Reputation: 735

SQL Server: Unique Composite Key that looks order

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.

Upvotes: 2

Views: 397

Answers (2)

Charlieface
Charlieface

Reputation: 71168

Two relatively simple options present themselves.

1. A check constraint and "instead of" trigger

If we create a constraint pkId1 <= pkId2, then the requirement can be satisfied, as noted in SMor's comment. However, the problem is that this also blocks an insert of (2,1).

To solve this, we create an INSTEAD OF trigger on the table that swaps the values:

CREATE TRIGGER TrgINS ON dbo.Table
INSTEAD OF INSERT AS

SET NOCOUNT ON;

INSERT dbo.Table (pkId1, pkId2)
SELECT
  IIF(pkId1 > pkId2, pkId2, pkId1),
  IIF(pkId1 > pkId2, pkId1, pkId2)
FROM inserted;

GO

2. An indexed view to enforce the constraint

We create an indexed view on the table with the values swapped. The unique constraint on the view itself enforces the requirement, so a single insert of (2,1) is not blocked, a further insert of either (2,1) or (1,2) is blocked:

CREATE VIEW vwTableUnique
WITH SCHEMABINDING AS

SELECT
  IIF(pkId1 > pkId2, pkId2, pkId1) AS pkId1,
  IIF(pkId1 > pkId2, pkId1, pkId2) AS pkId2
FROM dbo.Table;

GO

CREATE UNIQUE CLUSTERED INDEX CX_vwTableUnique ON dbo.Table (pkId1, pkId2);

GO

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 81930

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

Upvotes: 2

Related Questions