CODEWITHSUNDEEP
sql-serverdatabase-designunique-constraint
Joth
Joth

Reputation: 165

Set a unique constraint across multiple table columns

I am creating a table in SQL Server with the following columns: 

ic_card_num, 
employee_id, 
active

multiple duplicates of ic_card_num and active are okay if only one card is active:

employee_id | ic_card_num |  active       
   123      |    111      |   false      
   235      |    111      |   true      
   987      |    111      |   false

I want to avoid this:

employee_id | ic_card_num |  active       
   123      |    111      |   true      
   235      |    111      |   true      
   987      |    111      |   false

I am working with SQL Server, is there a way to ensure that I don't allow more than one active id_card_num record in my table?

Upvotes: 0

Views: 514

Answers (1)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88951

Use a unique filtered index:

create table ct
(
  ic_card_num int,
  employee_id int,
  active bit
)

create unique index ak_OnlyOneActive_ic_card 
on ct(ic_card_num) 
where active = 1

insert into ct(employee_id, ic_card_num, active)
values (123,111,1),(978,111,0)
go

insert into ct(employee_id, ic_card_num, active)
values (235,111,1)
--Cannot insert duplicate key row in object 'dbo.ct' with unique index 'ak_OnlyOneActive_ic_card'. The duplicate key value is (111).

Upvotes: 5

Related Questions