SQL Server: "Mostly-unique" index

Posted by Ian Boyd on Stack Overflow See other posts from Stack Overflow or by Ian Boyd
Published on 2010-05-12T17:04:34Z Indexed on 2010/05/12 17:14 UTC
Read the original article Hit count: 282

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?

© Stack Overflow or respective owner

Related posts about sql-server

Related posts about sql-server-2000