CODEWITHSUNDEEP
t-sqlsql-server-2008
slandau
slandau

Reputation: 24052

Prevent dupe records in SQL Server

So I have this stored procedure to insert a message into my database. I wanted to prevent users from posting duplicate messages within a short period of time, whether on accident or on purpose (either a laggy connection or a spammer).

This is what the insert statement looks like:

IF NOT EXISTS (SELECT * FROM tblMessages WHERE message = @message and ip = @ip and datediff(minute,timestamp, getdate()) < 10)
    BEGIN
        INSERT INTO tblMessages (ip, message, votes, latitude, longitude, location, timestamp, flags, deleted, username, parentId)
        VALUES (@ip, @message, 0, @latitude, @longitude, @location, GETDATE(), 0, 0, @username, @parentId)
    END

You can see I check to see if the same user has posted the same message within 10 minutes, and if not, I post it. I still saw one dupe come through yesterday. When I checked the timestamp of both messages in the database, they were exactly the same, down to the second, so I'm guessing when this 'exists' check ran on each insert, both came back empty, so they both inserted fine (at basically the same exact time).

What's a way I can prevent this from happening correctly?

Upvotes: 0

Views: 198

Answers (4)

gbn
gbn

Reputation: 432180

I reckon you need a trigger

A unique constraint/index isn't clever enough to deal with the 10 minute gap between posts for a given message and ip.

CREATE TRIGGER TRG_tblMessages_I FRO INSERT
AS
SET NOCOUNT ON;

IF EXISTS (SELECT * 
       FROM tblMessages M
           JOIN INSERTED I ON M.message = I.message and M.ip = I.ip
       WHERE
           datediff(minute, M.timestamp, I.timestamp) < 10)
BEGIN
    RAISERRROR ('blah', 16, 1)
    ROLLBACK TRAN
END

Edit: you need an extra condition to ignore the same row you have just inserted (eg using surrogate key)

Upvotes: 1

Factor Mystic
Factor Mystic

Reputation: 26760

Actually Derek Kromm isn't far off; Essentially you do want a unique constraint, you just want range for one of the columns.

You can express this as a filtered index which enforces the uniqueness on the columns you want but with a filter to match timestamps within a 10 minutes range.

CREATE NONCLUSTERED INDEX IX_UNC_tblMessages
ON tblMessages (message, ip, timestamp)
WHERE datediff(minute, timestamp, getdate()) < 10)

On the difference between a unique constraint and a filtered index which maintains uniqueness (MSDN):

There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. Data validation occurs in the same manner and the query optimizer does not differentiate between a unique index created by a constraint or manually created. However, you should create a UNIQUE or PRIMARY KEY constraint on the column when data integrity is the objective. By doing this the objective of the index will be clear.

The only aspect of this I'm not sure about is the use of getdate(). I'm not sure what effect that will have on the index and performance- this you will want to test for yourself.

Upvotes: 1

Alex_L
Alex_L

Reputation: 2666

I think, the easiest way is to use a triggers to check the sender and body of message in existing records in the table.

or, as Derek said, you can use the constraint, but with another condition:

ALTER TABLE tblMessages ADD CONSTRAINT uq_tblMessages UNIQUE (message,ip,username, parentId)

but constraint will generate exception (and you will need to handle it).

Upvotes: 0

Derek
Derek

Reputation: 23228

Add a unique constraint to the table to absolutely prevent it from happening

ALTER TABLE tblMessages ADD CONSTRAINT uq_tblMessages UNIQUE (message,ip,timestamp)

Upvotes: 0

Related Questions