Conditional Unique Constraint

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;

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

conditional unique constraint on multiple columns in oracle

You seem to want a 3-part index:

create unique index my_index
    on my_table (case when f_id <> 3 then c_id end,
                 case when f_id <> 3 then o_id end,
                 case when f_id <> 3 then f_id end
                );

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

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").

Create conditional unique constraints on multiple columns

I believe this is sufficient:

create unique index  on  (case when a = 1 then  end, 
                                                  case when a = 1 then  end); 

Postgresql: Conditionally unique constraint

PostgreSQL doesn't define a partial (i.e. conditional) UNIQUE constraint - however, you can create a partial unique index.

PostgreSQL uses unique indexes to implement unique constraints, so the effect is the same, with an important caveat: you can't perform upserts (ON CONFLICT DO UPDATE) against a unique index like you would against a unique constraint.

Also, you won't see the constraint listed in information_schema.

CREATE UNIQUE INDEX stop_myc ON stop (col_a) WHERE (col_b is NOT null);

See partial indexes.

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'

---

Related Topics