SQL Server: "Mostly-unique" index

Question

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?

Answer 1

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

Answer 2

Yes

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

Edit:

• Indexed view example

Answer 3

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.

Answer 4

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