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?

Thom A
  • 88,727
  • 11
  • 45
  • 75
BiigJiim
  • 165
  • 1
  • 1
  • 10

1 Answers1

3

It could be implemeneted using filtered index:

CREATE UNIQUE INDEX udx ON tbl_Sumbissions(CaseID)
WHERE AuthorisedStatus = 'Authorised'
Lukasz Szozda
  • 162,964
  • 23
  • 234
  • 275