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?

回答1:

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

回答2:

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 
)

回答3:

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