CODEWITHSUNDEEP
sqldatabasesqlite
user1425798
user1425798

Reputation: 335

SQLite: Preventing Duplicate Rows

CREATE TABLE Permission ( 
    permissionID INTEGER PRIMARY KEY UNIQUE,
    user         INTEGER
    location     INTEGER 
);

I don't want to have user or location to be UNIQUE because I can have multiple rows with user containing the same data, or multiple rows with location containing the same data. I just want to avoid having both user and location having some value, with that row repeating any number of times.

Ex: this is okay

permissionID user location
--------------------------
      1        1     2
      2        2     2
      3        2     1

but this is not okay:

permissionID user location
--------------------------
      1        1     2
      2        1     2

because a row already exists in which user = 1 and location = 2.

How can I avoid duplicates?

Upvotes: 20

Views: 35276

Answers (4)

Martin Böschen
Martin Böschen

Reputation: 1769

What you essentially want to do is to make the combination of user and location unique across you table. There are several options to achieve that and I will describe these options in the order you should consider them, as the former options are more natural than the latter.

Option 1: Have a unique constraint in your table

You can put the constraint you want directly in your table:

CREATE TABLE Permission (
    permissionID INTEGER PRIMARY KEY UNIQUE,
    user         INTEGER,
    location     INTEGER 
    unique (user, location)
);

This is the most natural option to express your requirement. The caveat is that it is not so easy to add and remove this option on an existing table. See the Annex 2 of this post on how to add this option to an existing table.

If you now try to insert a duplicate entry into the table you get the following result:

sqlite> insert into Permission (user, location) values (1, 2);
Error: UNIQUE constraint failed: Permission.user, Permission.location

Option 2: Create a unique index

It is also possible to create auniqe index

CREATE TABLE Permission ( 
    permissionID INTEGER PRIMARY KEY UNIQUE,
    user         INTEGER,
    location     INTEGER 
);
CREATE UNIQUE INDEX user_location ON Permission (user,location);

If you try to insert a duplicate entry with this option you get the exact same error message as in the first option:

sqlite> insert into Permission (user, location) values (1, 2);
Error: UNIQUE constraint failed: Permission.user, Permission.location

You might ask about the difference between this option and the first one, and so have many others . As the sqlite documentation explains, internally it is probably implemented in the exact same way. It really boils down to the fact, that it is much easier to add and drop an index from a table than to add and remove a unique constraint on a table.

Option 3: Use a trigger

For the sake of completeness, it is also possible to use a trigger to prevent duplicates to be inserted, albeit I hardly can imagine a reason why you should prefer this option. It is the most general way to react on an INSERT and it could look like this for your example:

CREATE TRIGGER avoid_duplicate_user_locations
   BEFORE INSERT
   ON Permission
   when exists (select * from Permission where user = new.user and location = new.location)
BEGIN
SELECT
     RAISE (ABORT,'duplicate entry');
END;

If you try to insert a duplicate entry with this option, you will run into the error message you specified in the trigger:

sqlite> insert into Permission (user, location) values (1, 2);
Error: duplicate entry

Annex 1: Removing existing Duplicates

If you already have duplicates in your table, the following code will help you you to remove them. If you want to apply the first or second option, you will have to do it.

DELETE FROM Permission 
  WHERE permissionID NOT IN 
    (SELECT MIN(permissionID) FROM Permission GROUP BY user,location );

Annex 2: Adding a unique contraint to table

If you habe created the schema for the table without the UNIQUE constraint, here is a recipe how to add it.

CREATE TABLE Permission2 (
    permissionID INTEGER PRIMARY KEY UNIQUE,
    user         INTEGER,
    location     INTEGER, 
    unique (user, location)
);

INSERT INTO Permission2
SELECT *
FROM Permission;

DROP Table Permission;

ALTER TABLE Permission2
RENAME TO Permission;

Upvotes: 9

Pரதீப்
Pரதீப்

Reputation: 93694

First option is creating a unique constraint on user and location columns which will avoid duplicates. 

Create unique index permission_user_location  on permission (user,location);

Else if you already have duplicate rows in table you need to delete the duplicate rows and create unique constraint

To remove duplicate rows use this query

DELETE
FROM Permission 
WHERE NOT EXISTS 
(
select 1 from 
(
select min(permissionID) as permissionID, user, location
From Permission A
Group by user, location
) B
Where B.permissionID = Permission.permissionID
AND   B.user = Permission.User
AND   B.location = Permission.location 
)

Upvotes: 3

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95532

Declare a unique constraint on (user, location). 

CREATE TABLE Permission (
    permissionID integer primary key,
    user integer not null,
    location integer not null,
    unique (user, location)
);

sqlite> insert into Permission (user, location) values (1, 2);
sqlite> insert into Permission (user, location) values (1, 2);
Error: UNIQUE constraint failed: Permission.user, Permission.location

Upvotes: 32

Bob
Bob

Reputation: 1055

CREATE TABLE Permission ( 
    permissionID INTEGER PRIMARY KEY UNIQUE,
    user         INTEGER,
    location     INTEGER 
);
CREATE UNIQUE INDEX user_location ON Permission (user,location);

Upvotes: 2

Related Questions