Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have this table:

CREATE TABLE [tblExample](
    [ExampleID] [int] IDENTITY(1,1) NOT NULL,
    [WordsAndStuff] [nvarchar](max) NOT NULL,
    [Active] [bit] NOT NULL

I want the Active column to have a unique constraint that will only allow one record to be true (1). At this point, I don't need there to BE a true record all the time, there just cannot be more than one of them.

How do I write the constraint?

like image 916
crackedcornjimmy Avatar asked Nov 06 '17 16:11

crackedcornjimmy


Video Answer


1 Answers

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
like image 74
SqlZim Avatar answered Oct 24 '22 11:10

SqlZim