CODEWITHSUNDEEP
sql-server
raisul
raisul

Reputation: 640

Unique constraint on two columns for any combination in SQL Server?

I have two columns 'Col_1' and 'Col_2' in the database. I want to apply a unique constraint so that Col_1-Col_2 and Col_2-Col_1 both combinations can be blocked.

Lets say, existing data combinations are:

Col_1    Col_2 
 ABC      DEF
 QWE      ZXC
 ASD      VBN

Now the following data insertion combination should not be allowed:

Col_1    Col_2 
 ABC      DEF
 DEF      ABC
 ZXC      QWE

Upvotes: 0

Views: 838

Answers (1)

SteveC
SteveC

Reputation: 6015

The constraint may be created either when the table is created or by altering an existing table.

Create a new table

create table SomeTable(
  Col_1     varchar(20),
  Col_2     varchar(20),
  constraint unq_SomeTable_c1_c2 unique(Col_1, Col_2));
go

Alter an existing table

alter table SomeTable
add
  constraint unq_SomeTable_c1_c2 unique(Col_1, Col_2);

If duplicate insert is attempted the error message will be as follows

Msg 2627, Level 14, State 1, Line 7502
Violation of UNIQUE KEY constraint 'unq_SomeTable_c1_c2'. Cannot insert duplicate key in object 'dbo.SomeTable'. The duplicate key value is (ABC, DEF).
The statement has been terminated.

[EDIT] to ensure the order of the columns does not matter I created a TRIGGER which runs after INSERT or UPDATE. The error message could be changed to whatever you wish.

create trigger trg_SomeTable_unq_c2c1_ins_upd on SomeTable
after insert, update
as
if exists(select 1 
            from SomeTable st join inserted i on st.Col_2=i.Col_1
                                            and st.Col_1=i.Col_2)
    rollback transaction;
go

Test Case

insert SomeTable(Col_1, Col_2) values
('ABC', 'DEF'),
('QER', 'ZXC'),
('ASD', 'VBN');

insert SomeTable(Col_1, Col_2) values
('DEF', 'ABC');

Output

    (3 row(s) affected)
    
Msg 3609, Level 16, State 1, Line 7514
The transaction ended in the trigger. The batch has been aborted.

Upvotes: 2

Related Questions