Microsoft SQL Server : enforce uniqueness for certain combinations of composite key pairs

Question

A non-archived barcode must only appear once in the table.

All archived barcodes do not need to be unique

My table has these columns

1. ID int (generated by database)
2. Barcode nvarchar(50)
3. Archived bit

Sample data

ID   |Barcode    |Archived
-----|-----------|--------
1    |AB1234US   | 0
2    |AB1234US   | 0         <-- NOT allowed, this is a duplicate and not archived
3    |AB1234US   | 1         <-- yes allowed, this is archived
4    |AB1234US   | 1         <-- ditto
5    |CD6789JP   | 0         
6    |EF9012YE   | 0
7    |EF9012YE   | 1         <-- allowed

What I've tried.

I've added a composite key made up of the barcode and the archived flag. But this will not allowed more than one archived barcode.

ALTER TABLE [Parcels] ADD  CONSTRAINT [Barcode_Archived] UNIQUE 
NONCLUSTERED 
(
    [Barcode] ASC,
    [Archived] ASC

Question How to force uniqueness for non-archived barcodes but not for archived barcodes?

Answer 1

You can simply use a unique filtered index, eg

create table Parcels(id int identity primary key, barcode nvarchar(50), archived bit)

create unique index uk_Parcels_Barcode_unarchived 
on Parcels(barcode,archived)
where archived = 0

insert into parcels(barcode,archived) values ('AB1234US',0)
insert into parcels(barcode,archived) values ('AB1234US',1)
insert into parcels(barcode,archived) values ('AB1234US',1)

insert into parcels(barcode,archived) values ('AB1234US',0) --fails

outputs

(1 row affected)

(1 row affected)

(1 row affected)
Msg 2601, Level 14, State 1, Line 13
Cannot insert duplicate key row in object 'dbo.Parcels' with unique index 'uk_Parcels_Barcode_unarchived'. The duplicate key value is (AB1234US, 0).