Table constraint

Transact-SQL
1

SQL2008R2

I have not used constraint checks more that a couple times, so I am not overly familiar with it.

CREATE TABLE ExpressConsent(
	IDX                 INT IDENTITY(1,1) NOT NULL PRIMARY KEY, 
	RefNum              VARCHAR(10) NOT NULL, 
	Phone               VARCHAR(10) NOT NULL, 
	ConsentDate         DATETIME NOT NULL DEFAULT GETDATE(), 
	ConsentBy           VARCHAR(100) NOT NULL, 
	ConsentAgent        INT NOT NULL, 
    ConsentType         VARCHAR(10), 
    IsDeletedDate       DATETIME NULL, 
    HistoryNoteIDX      INT NULL  
); 
GO

What I need to do is make sure my table only has one record with unique Phone + Refnum where IsDeletedDate is null. It can have multiple records with IsDeletedDate as not null. Note, I know I could just put a unique index which would work until someone puts in the same date without time into IsDeletedDate for a given phone and refnum.

Thank you for your time,
DJJ

2

If you are on SQL 2008 or later, perhaps you could create a filtered unique index

CREATE UNIQUE NONCLUSTERED INDEX [SomeIndexName] ON dbo.ExpressConsent
(
	Phone ASC, RefNum ASC
)
WHERE IsDeletedDate IS NULL;
1 Like
3

I had told a coworker, we could use a filtered index but got to thinking about constraints.
Thank you @JamesK