SQL Server: Most Unique Index

Question

In the table, I want to make sure that for a key with five columns, only unique values ​​exist:

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 want the only restriction to apply between the lines when the 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

Yes

• SQL Server 2008: Use a Filtered Index
• SQL Server 2005: use a trigger or indexed view

Edit:

• Indexed View Example

Answer 2

What is an EventAction?

I suspect there is a design issue here and you might want to create a table / relation for each type of EventAction. This will allow you to create a unique constraint for the InsertEventAction table, for example.

Perhaps you can indicate a business context for your question.

Following the answers to the comments: Given the nature of the data source and the parsing activity you want to implement, I think gbn has suggested your best options.

The infamous database is also not SQL Server, since you could implement your own audit engine using triggers. Such a solution may include your “filtering” logic in Trigger.

Answer 3

I would think of this with a control limit. I do not think that the traditional unique restriction will work.

Answer 4

This is an example of an INSTEAD OF INSERT trigger. EXISTS checks for existing rows with the same values, but only when the 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