CODEWITHSUNDEEP
sql-server-2008multiple-columnsunique-constraint
peterp
peterp

Reputation: 171

Unique constraint on 2 columns, but in normal and reverse order

Couldn't find any answer, so I'm writing this open question. I'm curious if is there any possibility to create such UNIQUE constraint on 2 columns in SQL Server 2008 table, that "normal" and "reverse" duplicates wouldn't be allowed.

Example:

ID1 = 10, ID2 = 20   -- existing row

Trying to add a pair of values:

ID1 = 10, ID2 = 20   -- not allowed because of the UNIQUE key
ID1 = 20, ID2 = 10   -- allowed

The second row will be inserted (of course it's not a duplicate). And that's the issue. Can any key/constraint/whatever be set on a table to disallow above insertion? I.e. something using an expression instead of list of columns? For now I use a trigger which checks for such "duplicates", but I just wonder if is there any simpler solution.

Thanks, Peter P.

Upvotes: 2

Views: 555

Answers (2)

Tim Burris
Tim Burris

Reputation: 162

I just needed the exact same thing (just a couple years later)

I decided to go with a check constraint that requires ID1 to be less than ID2

i know that's kinda of hacky, which is why i'm not convinced it's better than the trigger.

now when i insert data, ID1 has to be the smaller else the check constraint fails, that coupled with the unique constraint ensures only one instance of the combination exists.

I'm not anti-trigger, just prefer to not use them unless i really need to

--create your table
create table dbo.test
(
    ID1 int not null,
    ID2 int not null
)
go

--create a unique constraint the prevents duplicate of id1 and id2
create unique index test_ID1_ID2_uindex
    on test (ID1, ID2)
go

--create a check that ensures id1 is less than id2, disallowing something like Id1=20, id2=10
ALTER TABLE dbo.Test
ADD CONSTRAINT CHK_Test_ID1_Less_ID2 CHECK (ID1<ID2);

GO

--this will fail
 insert dbo.test
    select 10,20
    union
    select 20,10

Upvotes: 0

Nishad
Nishad

Reputation: 426

    CREATE TABLE dbo.test 
    (ID1 int , ID2 int ,
    CONSTRAINT ID_UK UNIQUE(ID1,ID2),

    )

    GO


    IF EXISTS (SELECT name FROM sysobjects
    WHERE name = 'check_val' AND type = 'TR')
    DROP TRIGGER check_val
    GO
    CREATE TRIGGER check_val
    ON dbo.test
    FOR INSERT, UPDATE 
    AS
    if exists ( select i.ID1 ,i.ID2 from inserted i inner join dbo.test t 
    on t.ID2=i.ID1 and t.ID1=i.ID2 )

    RAISERROR ('duplicate values',
    16, 1)
    ROLLBACK TRANSACTION

    GO

    insert dbo.test 
    select 10,20
    union
    select 20,10

Upvotes: 1

Related Questions