Thursday, 2 June 2011

T-SQL: Enforce uniqueness with Filtered Indexes

A common design I see is tables designed with an Is_Live flag. I don't have any problem with this in general although it does throw up a slight issue when trying to enforce uniqueness within the table e.g when you're trying to ensure that there is only one "Live" record across a unique set of columns. Previous to SQL 2008 your only option was to use a trigger to enforce the uniqueness of the data but now you can harness the power of Filtered Indexes to achieve this goal.

Allow me to demonstrate:

CREATE TABLE dbo.Marriages
Husband NVARCHAR(255),
Wife NVARCHAR(255),
INSERT INTO dbo.Marriages
('Liam Gallagher', 'Patsy Kensit', 0), ('Liam Gallagher', 'Nicole Appleton', 1),
'Dan Donovan', 'Patsy Kensit', 0), ('Jim Kerr', 'Patsy Kensit', 0), ('Jeremy Healy', 'Patsy Kensit', 0),
'Andre Agassi', 'Brooke Shields', 0), ('Andre Agassi', 'Steffi Graf', 1),
'Peter Andre', 'Katie Price', 0), ('Alex Reid', 'Katie Price', 0),
'Prince Charles', 'Diana Spencer', 0), ('Prince Charles', 'Camilla Parker Bowles', 1)
-- this fails as some husbands have multiple marriages
ALTER TABLE dbo.Marriages ADD CONSTRAINT UQ_Marriages_HusbandIsCurrent UNIQUE (Is_Current)
The CREATE UNIQUE INDEX statement terminated because a duplicate KEY was found FOR the object name 'dbo.Marriages' AND the INDEX name 'UQ_Marriages_HusbandIsCurrent'. The duplicate KEY >value IS (0).

-- this succeeds because we're only interested in ensuring someone only has 1 current wife
CREATE UNIQUE NONCLUSTERED INDEX UIDX_Marriages_WifeIsCurrent_Filt ON dbo.Marriages(Wife, Is_Current) WHERE Is_Current = 1
-- so now we can insert a new marriage
INSERT INTO dbo.Marriages SELECT 'Jay-Z', 'Beyonce', 1

-- but if I try and marry Beyonce...
INSERT INTO dbo.Marriages SELECT 'Richard Brown', 'Beyonce', 1

Cannot INSERT duplicate KEY row IN object 'dbo.Marriages' WITH UNIQUE INDEX 'UIDX_Marriages_WifeIsCurrent_Filt'. The duplicate KEY value IS (Beyonce,1).

-- (although I can be a divorcee!!)
INSERT INTO dbo.Marriages SELECT 'Richard Brown', 'Beyonce', 0

1 comment:

/* add this crazy stuff in so i can use syntax highlighter