Unique Constraint Controlled by a Bit Column

UNIQUE constraint controlled by a bit column

You can have a unique index, using the SQL Server 2008 filtered indexes feature, or you can apply a UNIQUE index against a view (poor man's filtered index, works against earlier versions), but you cannot have a UNIQUE constraint such as you've described.

An example of the filtered index:

 CREATE UNIQUE NONCLUSTERED INDEX IX_FieldsOnForms_NonDeletedUnique ON FieldsOnForms (FieldID,FormID) WHERE isDeleted=0

Unique Constraint for Bit Column Allowing Only 1 True (1) Value

Just one active record at a time in the table? You can use a unique index with a filter:

create unique nonclustered index uixf_tblExample_Active_filtered
  on tblExample (Active)
    include (ExampleId, WordsAndStuff) -- optional included columns
  where Active=1

T-SQL Unique Constraint WHERE AnotherColumn = ParticularValue

Using an indexed view to implement a "filtered index" on versions of SQL Server predating 2008:

CREATE VIEW vOnlyOneActive
AS
  SELECT PersonID
  FROM <underlying table>
  WHERE Active = 1
GO
CREATE UNIQUE CLUSTERED INDEX IX_vOnlyOneActive on vOnlyOneActive (PersonID)
GO

You'll need to have the right ANSI settings turned on for this.

Unique constraint within a group of records where some value is the same

Using UDFs in check constraints can fail under snapshot isolation or multirow updates.

Assuming that all your fk1 and pk1 values are currently (and will always be) positive you could create a computed column with the following definition

CASE WHEN isPrimary = 1 THEN fk1 ELSE -pk1 END

then add a unique constraint to that. Or if that assumption can't be made then maybe

CASE WHEN isPrimary = 0 THEN 1.0/pk1 ELSE fk1 END

Create a unique constraint where one column can be null to prevent duplicate record insertion

You cannot have a WHERE clause in a constraint definition, but a partial unique index will do the trick:

CREATE UNIQUE INDEX ON cars (user_id, name) 
WHERE purchased_at IS NULL;

---

Related Topics