SQL Unique Constraint on Subset of data in table [duplicate]

Question

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?

Lukasz Szozda · Accepted Answer · 2022-04-04 17:47:00Z

3

It could be implemeneted using filtered index:

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

answered Apr 4, 2022 at 17:47

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Unique Constraint on Subset of data in table [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related