SQL RowStatus (Only 1 value to be 'Current )

Transact-SQL
1

i have a RowStatus column, which i only want 1 instance of 'Active' per ID ( ID is not a primary key. ) has anyone come across this sort of requirement and how have you got round it.

First thing that comes to mind is a constraint,??

Thank you in advance.

2

Normally you would use a unique constraint, but that would also limit you to only 1 Inactive row per ID as well. It sounds like you want to allow multliple Inactive rows. In that case you would use a filtered index in SQL Server:

CREATE UNIQUE INDEX UniqueActive ON myTable(ID)
WHERE RowStatus='Active';
3

yes thats excellent,
thank you

4

You can use a constraint w/o filtering, but if you are able to use filtered indexes, that's probably the best choice