CODEWITHSUNDEEP
sqlsql-servert-sql
Jimmyt1988
Jimmyt1988

Reputation: 21126

SQL conditional constraint on multiple columns being unique

I would like to have a conditional constraint on my database:

enter image description here

You can see that my CreatedBy column has a few entries missing due to legacy data problems.

My new code ensures the CreatedBy is filled out.

Question:

I would like to ensure that noone can enter duplicate entries into the db by adding a conditional constraint. that defines that a row should be unique by AnswerID, PollID and CreatedBy... only if CreatedBy is not empty

It might look like this:

CONSTRAINT UNIQUE BY AnswerID, PollID, CreatedBy WHERE CreatedBy IS NOT NULL

Any ideas if it's possible?

Upvotes: 3

Views: 1011

Answers (2)

cloudsafe
cloudsafe

Reputation: 2504

If version is prior to 2008:

CREATE VIEW dbo.vw_Tablename
WITH SCHEMABINDING AS
SELECT AnswerID, PollID, CreatedBy
FROM dbo.Tablename
WHERE CreatedBy IS NOT NULL;

GO

CREATE UNIQUE CLUSTERED INDEX UIX_vw_Tablename 
  ON dbo.vw_Tablename(AnswerID, PollID, CreatedBy)

Upvotes: 1

Kittoes0124
Kittoes0124

Reputation: 5080

Sure, use a filtered index:

CREATE UNIQUE NONCLUSTERED INDEX <indexName>
    ON <tableName> (AnswerID, PollID, CreatedBy)  
    WHERE CreatedBy IS NOT NULL;

Or, since you said "not empty" then something like this should do:

CREATE UNIQUE NONCLUSTERED INDEX <indexName>
    ON <tableName> (AnswerID, PollID, CreatedBy)  
    WHERE CreatedBy IS NOT NULL
      AND CreatedBy <> N'';

Upvotes: 6

Related Questions