How to insert a record and make sure the entire row is unique

Question

I want to insert multiple values into a row, but I want to make sure that the row is unique, i.e. no duplicate rows.

I am unsure how to do this (it is fairly easy to if there is only a single value to check for i.e. like this: SQL Server - How to insert a record and make sure it is unique).

This is my code, but it won't allow me to insert unique rows as it tests for single columns and multiple columns combined.

CREATE TABLE myCities (
    UserID int null,
    CityID int null 
)

DECLARE @UserID int, @CityID int
SET @UserID = 1
SET @CityID = 1

INSERT INTO myCities (UserID,CityID) 
SELECT @UserID,@CityID
        WHERE 
            @UserID NOT IN ( SELECT UserID FROM myCities WHERE UserID = @UserID )
        AND 
            @CityID NOT IN ( SELECT CityID FROM myCities WHERE CityID = @CityID )

Are you doing insert into selects and only one user and thread at a time? — mmmmmm, Jul 17 '12 at 09:32

score 5 · Answer 1 · answered Jul 16 '12 at 23:06

5

The only sure way is to put the check in the database. In this case create a unique key on the table which will also be its primary key so

-- syntax for MS/Sybase at least is
ALTER TABLE myCities
ADD CONSTRAINT uc_myCities UNIQUE (UserID,CityID)

Then when you insert a duplicate then you will get an error and your code will have to deal with it.

answered Jul 16 '12 at 23:06

mmmmmm

32,227
27
88
117

Thanks - this solution won't work in my scenario because I am doing some manual one-time-only updating of various tables, copying some data around etc. My tables are fairly large (100+ million rows) - the myCities was just to illustrate my problem. Thanks for the inputs though. – Sha Jul 16 '12 at 23:17

score 4 · Accepted Answer · answered Jul 16 '12 at 22:59

4

Sometimes the obvious is right at hand - solved it by using NOT EXISTS, like this:

INSERT INTO myCities (UserID,CityID) 
SELECT @UserID,@CityID
        WHERE NOT EXISTS ( 
                   SELECT UserID FROM myCities 
                   WHERE 
                   UserID = @UserID and 
                   CityID = @CityID
                 )

answered Jul 16 '12 at 22:59

Sha

2,185
1
36
61

Where did you get that so quickly (as soon as you posted your question)? – Lion Jul 16 '12 at 23:00
Well, I figured it out while I was posting. I could have chosen not to post, but maybe somebody else could benefit from it. – Sha Jul 16 '12 at 23:03
Mark, OK - how would you manage that? Put it in a transaction? I am using SQL Server. – Sha Jul 16 '12 at 23:07

How to insert a record and make sure the entire row is unique

2 Answers2