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,