SQL How to Have a "Conditionally Unique" Constraint on a Table

conditional unique constraint

Add a check constraint like this. The difference is, you'll return false if Status = 1 and Count > 0.

http://msdn.microsoft.com/en-us/library/ms188258.aspx

CREATE TABLE CheckConstraint
(
  Id TINYINT,
  Name VARCHAR(50),
  RecordStatus TINYINT
)
GO

CREATE FUNCTION CheckActiveCount(
  @Id INT
) RETURNS INT AS BEGIN

  DECLARE @ret INT;
  SELECT @ret = COUNT(*) FROM CheckConstraint WHERE Id = @Id AND RecordStatus = 1;
  RETURN @ret;

END;
GO

ALTER TABLE CheckConstraint
  ADD CONSTRAINT CheckActiveCountConstraint CHECK (NOT (dbo.CheckActiveCount(Id) > 1 AND RecordStatus = 1));

INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 2);
INSERT INTO CheckConstraint VALUES (1, 'No Problems', 1);

INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 2);
-- Msg 547, Level 16, State 0, Line 14
-- The INSERT statement conflicted with the CHECK constraint "CheckActiveCountConstraint". The conflict occurred in database "TestSchema", table "dbo.CheckConstraint".
INSERT INTO CheckConstraint VALUES (2, 'Oh no!', 1);

SELECT * FROM CheckConstraint;
-- Id   Name         RecordStatus
-- ---- ------------ ------------
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  2
-- 1    No Problems  1
-- 2    Oh no!       1
-- 2    Oh no!       2

ALTER TABLE CheckConstraint
  DROP CONSTRAINT CheckActiveCountConstraint;

DROP FUNCTION CheckActiveCount;
DROP TABLE CheckConstraint;

SQL can I have a conditionally unique constraint on a table?

If you are using SQL Server 2008 a Index filter would maybe your solution:

http://msdn.microsoft.com/en-us/library/ms188783.aspx

This is how I enforce a Unique Index with multiple NULL values

CREATE UNIQUE INDEX [IDX_Blah] ON [tblBlah] ([MyCol]) WHERE [MyCol] IS NOT NULL

Conditional Unique Constraint SQL

It sounds like you've tried to squash two or more tables in to one table.

It's hard to tell without more background

For example, if you made a big flat file you might have this?

a	b	c	d	x	y	z

1	2	3	1	1	3	1
1	2	3	1	2	8	7
1	2	3	1	5	9	2

4	5	6	2	9	8	7
4	5	6	2	4	5	6
4	5	6	2	3	2	1
4	5	6	2	2	1	0

How to create a constraint for conditional unique values?

You can't create a CONSTRAINT for that, however, you can create a filtered unique index:

USE Sandbox;
GO

CREATE TABLE dbo.Student (ID int IDENTITY(1, 1) PRIMARY KEY,
                          FirstName varchar(100),
                          LastName varchar(100),
                          Active bit);

CREATE UNIQUE INDEX UQ_StudentName
    ON Student (FirstName,LastName)
    WHERE Active = 1;
GO

INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 1); --Success
GO
INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 0); --Success
GO
INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 0); --Success
GO
INSERT INTO dbo.Student (FirstName,
                         LastName,
                         Active)
VALUES ('Jane', 'Smith', 1); --Fails;
GO

UPDATE dbo.Student
SET Active = 1
WHERE ID = 2; --Fails;
GO

SELECT *
FROM dbo.Student;
GO

DROP TABLE dbo.Student;

I however, highly recommend against the thought that names are unique. I (personally) shared my name and date of birth with another person at several places in my youth and businesses that treated names (and date of birth) as unique on their systems were such a head ache for the both of us (there really were places where I (or they) couldn't register without using an abbreviated name because we "already existed").

How to create conditional unique constraint

Table Creation

CREATE TABLE CheckConstraint
(
  Name VARCHAR(50),
)
GO

Function Creation

create FUNCTION CheckDuplicateWithA() RETURNS INT AS BEGIN

  DECLARE @ret INT  =0 ;
  SELECT @ret = IsNull(COUNT(Name), 0) FROM CheckConstraint WHERE Name like '[A]%' group by Name having COUNT(name) >= 1;
  RETURN IsNUll(@ret, 0);
END;

GO

create FUNCTION CheckDuplicateOtherThenA() RETURNS INT AS BEGIN

  DECLARE @ret INT  =0 ;
  SELECT @ret = IsNull(COUNT(Name), 0) FROM CheckConstraint WHERE Name not like '[A]%' group by Name having COUNT(name) >= 1;
  RETURN IsNUll(@ret, 0);
END;

GO

Constraints

alter TABLE CheckConstraint
  add CONSTRAINT CheckDuplicateContraintWithA CHECK (NOT (dbo.CheckDuplicateWithA() > 2));
go

alter TABLE CheckConstraint
  add CONSTRAINT CheckDuplicateConmstraintOtherThenA CHECK (NOT (dbo.CheckDuplicateOtherThenA() > 1));
go

Result Set

insert into CheckConstraint(Name)Values('b')  -- Passed
insert into CheckConstraint(Name)Values('b')  -- Failed

insert into CheckConstraint(Name)Values('a')  -- Passed
insert into CheckConstraint(Name)Values('a')  -- Passed
insert into CheckConstraint(Name)Values('a')  -- Failed

Create conditional unique constraints on multiple columns

I believe this is sufficient:

create unique index <index_name> on <table_name> (case when a = 1 then <column_1> end, 
                                                  case when a = 1 then <column_2> end);

SQL Unique Constraint on Subset of data in table

It could be implemeneted using filtered index:

CREATE UNIQUE INDEX udx ON tbl_Sumbissions(CaseID)
WHERE AuthorisedStatus = 'Authorised'