CODEWITHSUNDEEP
sqlsql-servert-sql
matk
matk

Reputation: 1518

Index to enforce a single TRUE value per table in bit column

I have table that contains an IsDefault column:

CREATE TABLE CustomerType
(
    ID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NOT NULL,
    IsDefault bit NOT NULL
)

The IsDefault value should, naturally, be TRUE for only a single row, all other rows should be FALSE. I want to enforce this rule on the database level.

Currently I achieve this by adding a new computed column and placing a UNIQUE NONCLUSTERED INDEX on it:

CREATE TABLE CustomerType
(
    ID int IDENTITY(1,1) NOT NULL,
    Name nvarchar(50) NULL,
    IsDefault bit NOT NULL
    IsDefaultConstraint AS (CASE WHEN IsDefault = 1 THEN 1 ELSE -ID END),
)

CREATE UNIQUE NONCLUSTERED INDEX UQ_CustomerType_IsDefault ON CustomerType
(
    IsDefaultConstraint ASC
)

This works just fine, but has a bit of code smell to it because the extra column doesn't contain relevant data and is just used for enforcing the unique index.

Are there alternative ways to enforce the same behavior?

Upvotes: 1

Views: 819

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239734

For SQL Server 2008 or later, use a filtered index:

CREATE UNIQUE INDEX IX_Default on CustomerType (IsDefault) WHERE IsDefault = 1

For older versions, you use the "poor man's filtered index", the indexed view:

CREATE VIEW dbo.DRI_CustomerType_Default
WITH SCHEMABINDING
AS
   SELECT IsDefault FROM dbo.CustomerType WHERE IsDefault = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_Default on DRI_CustomerType_Default (IsDefault)

Upvotes: 3

Thorsten Kettner
Thorsten Kettner

Reputation: 95033

Unfortunately SQL-Server doesn't provide function based indexes, which is what you are looking for. So your approach is the best available.

If the additional column is too annoying, then use a view on the table hiding that column.

If this is still annoying you, switch to Oracle ;-)

Upvotes: 0

Related Questions