adc90 adc90 - 1 month ago 8
SQL Question

Making a column unique with one exception

We have an application whose work flow involves submitting information to an outside group and then inputting the user's id number into the system. For that reason we allow a set default value "00000000" to be put into the id field as a tentative value before the entry is approved and a permanent one is put in. What I'm looking for is essentially a way to ensure that the column remains unique except for that one value. What I'm basically looking for is a UNIQUE constraint, however instead of NULL being the blank option it being "00000000". I've considered doing it as part of a CHECK constraint, however that seems like it'd be a big performance hit. (Under the assumption that UNIQUE does some kind of indexing)

Answer

Use Filtered Index

as the Following:-

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notspecificvalue
ON YourTable(yourcolumn)
WHERE yourcolumn != "00000000";

Example:

-- Create Table
Create table Test (id int identity, code varchar (100))

-- Create Unique Filtered  Index
CREATE UNIQUE NONCLUSTERED INDEX idx_MyCol_Filtered 
ON Test(code)
WHERE code != '00000000';

-- Insert Dumy Data >> '00000000' is repeated and '0101' is once
insert into Test (code) 
Values  ('00000000'),
        ('00000000'),
        ('00000000'),
        ('0101')


select * from Test

The Result:

enter image description here

-- Now try inserting '0101' again
insert into Test (code) Values ('0101')

The Result:

enter image description here


For more details: Create Filtered Indexes