SQL Server – Unique Key Condition Based on Another Value

constraintsql serverunique-constraint

I have a table with the following columns:

ID, STRING_VALUE, ENUM_VALUE, CATEGORY_IDENTIFIER

I'd like to create a condition that won't allow two identical ENUM_VALUEs, but only if their ID is also the same.

For example:

1  BLUE    4  6   --> Good
2  BLUE    4  6   --> Good
3  BLUE    4  6   --> BAD
3  GREEN   4  6   --> BAD

Is there any way to create such a constraint?

Best Answer

Create a unique constraint on the 2 columns.

ALTER TABLE schemaName.tableName ADD CONSTRAINT
        uniTableName UNIQUE NONCLUSTERED
(ID, ENUM_VALUES)

Related Solutions

Sql-server – Unique Contraint/Index Based On Values

To enforce this declaratively in 2005 you can use an indexed view.

CREATE VIEW dbo.ActiveNames
WITH SCHEMABINDING
AS
SELECT Name
FROM  dbo.YourTable
WHERE Active = 1

GO

CREATE UNIQUE CLUSTERED INDEX UQ_ActiveNames ON dbo.ActiveNames(Name)

Sql-server – Unique constraint on multiple nullable columns Sql Server

There's nothing special that would prevent you from using a standard unique constraint here, unless there's something you aren't telling us.

When a unique constraint is created on a set of nullable columns, NULL is treated (in terms of uniqueness) like any other of the distinct values in the set. (I believe this behaviour varies across RDBMSes.)

So I think the only case you might have to protect against here is the where all three columns contain NULL at the same time. If this is invalid for your business logic, also create a CHECK constraint to disallow this combination.

Best Answer

Related Solutions

Sql-server – Unique Contraint/Index Based On Values

Sql-server – Unique constraint on multiple nullable columns Sql Server

Related Question