0

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?

Larnu
  • 76,706
  • 10
  • 34
  • 63
BiigJiim
  • 153
  • 1
  • 1
  • 9

1 Answers1

3

It could be implemeneted using filtered index:

CREATE UNIQUE INDEX udx ON tbl_Sumbissions(CaseID)
WHERE AuthorisedStatus = 'Authorised'
Lukasz Szozda
  • 139,860
  • 19
  • 198
  • 228