SQLite FAQ: How to create a unique constraint on multiple columns (fields)

SQLite FAQ: How do I create a UNIQUE constraint on multiple fields in a SQLite database table?

Just specify the unique constraint in your create table declaration, as shown here:

create table projects (
    _id integer primary key autoincrement,
    project_type text not null,
    name text not null,
    description text,
    last_updated datetime default current_timestamp,
    date_created datetime default current_timestamp,
    unique (project_type, name)
);

In this case I’ve stated that the combination of the project_type and name fields must be unique. As a result this first SQL INSERT statement will work:

insert into projects (project_type, name, description)
    values ('APPLICATION', 'ACME - Phase 1', 'ACME Project, Phase 1');

and this second INSERT attempt will fail:

insert into projects (project_type, name, description)
    values ('APPLICATION', 'ACME - Phase 1', 'ACME Project, Phase 1 (this fails)');

On a related note, if you just want to add a UNIQUE constraint to one field in your table, you can use this syntax instead:

name text not null unique,

But to have a unique constraint on multiple fields, you need to use the syntax shown earlier:

unique (project_type, name)