SQLite: Preventing Duplicate Rows

Question

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?

Answer 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