CODEWITHSUNDEEP
sql
Sha
Sha

Reputation: 2397

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

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 )

Upvotes: 2

Views: 1749

Answers (2)

mmmmmm
mmmmmm

Reputation: 32681

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.

Upvotes: 5

Sha
Sha

Reputation: 2397

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
                 )

Upvotes: 4

Related Questions