Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server: "Mostly-unique" index

In a table i want to ensure that only unique vales exist over the five-column key:

Timestamp Account RatingDate TripHistoryKey EventAction
========= ======= ========== ============== ===========
2010511   1234    2010511    1              INSERT
2010511   1234    2010511    4              INSERT
2010511   1234    2010511    7              INSERT
2010511   1234    2010511    1              INSERT   <---duplicate

But i only want the unique constraint to apply between rows when EventAction is INSERT:

Timestamp Account RatingDate TripHistoryKey EventAction
========= ======= ========== ============== ===========
2010511   1234    2010511    1              INSERT
2010511   1234    2010511    1              UPDATE
2010511   1234    2010511    1              UPDATE   <---not duplicate
2010511   1234    2010511    1              UPDATE   <---not duplicate
2010511   1234    2010511    1              DELETE   <---not duplicate
2010511   1234    2010511    1              DELETE   <---not duplicate
2010511   1234    2010511    1              INSERT   <---DUPLICATE

Possible?

like image 604
Ian Boyd Avatar asked Feb 27 '23 01:02

Ian Boyd


1 Answers

Yes

  • SQL Server 2008: use a filtered index
  • SQL Server 2005: use a trigger or indexed view

Edit:

  • Indexed view example
like image 157
gbn Avatar answered Mar 07 '23 06:03

gbn