sql-serverunique-constraintunique-key

SQL Unique Constraint on Subset of data in table

I have a SQL Server table tbl_Submissions which contains a number of records for any given CaseID. Each Submission has an AuthorisedStatus field which can be "Authorised", "Rejected", "Rescinded". There could be multiple rejected or rescinded submissions for a CaseID, but there can only ever be one authorised record.

Is it possible to add a constraint or index to enforce this?

Solution

  • It could be implemeneted using filtered index:

    CREATE UNIQUE INDEX udx ON tbl_Sumbissions(CaseID)
WHERE AuthorisedStatus = 'Authorised'