SQL Server: "Mostly-unique" index

In a table i want to ensure that only uni开发者_运维知识库que 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?

Yes

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

Edit:

• Indexed view example

What is an EventAction?

I suspect that you have a design issue at work here and that you may want to consider creating a table/relation for each type of EventAction. Doing so would enable you to create a unique constraint on the InsertEventAction table for example.

Perhaps you can provide the business context to your question.

Following on from comments reply: Given the nature of the data source and the parsing activity you wish to implement I think gbn has suggested your best options.

It's a shame the source database is not also SQL Server as you could implement your own audit mechanism using Triggers. Such a solution could include your "filter" logic within the Trigger.

I would consider doing this with a check constraint. I don't think a traditional unique constraint will work.

This is an example of an "INSTEAD OF INSERT" trigger. The EXISTS checks if there are existing rows with the same values, but only when EventAction is INSERT.

CREATE TRIGGER [dbo].[YourTriggerName] ON [dbo].[YourTableName] 
   INSTEAD OF INSERT
AS 
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
  SET NOCOUNT ON;

  IF NOT EXISTS (
    SELECT 1
    FROM [YourTableName] L
    INNER JOIN inserted I ON
           I.Timestamp = L.Timestamp
       AND I.Account = L.Account
       AND I.RatingDate = L.RatingDate
       AND I.TripHistoryKey = L.TripHistoryKey
       AND I.EventAction = 'INSERT'
  )
  BEGIN
      INSERT INTO [YourTableName]
      SELECT * FROM inserted
  END
END