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),
  
Is_Current BIT NOT NULL DEFAULT(0)
)
GO
INSERT INTO dbo.Marriages
VALUES
  
('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)
GO
-- this fails as some husbands have multiple marriages
ALTER TABLE dbo.Marriages ADD CONSTRAINT UQ_Marriages_HusbandIsCurrent UNIQUE (Is_Current)
GO
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
GO
-- 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