Combination of two columns unique constraint

sqlite constraints unique-constraint
xralf picture xralf · May 8, 2012 · Viewed 7.7k times · Source

I created the table t1t2 which connects tables t1 and t2 as follows:

CREATE TABLE t1t2(
id integer primary key,
t1_id integer,
t2_id integer,
foreign key(t1_id) references t1(id),
foreign key(t2_id) references t2(id));

Is it possible to define a constraint (restriction) that enables only unique values of tuple (t1_id, t2_id)? Or should I check this in the application?

Answer

Larry Lustig picture Larry Lustig · May 8, 2012 
 CREATE UNIQUE INDEX idx_twocols ON t1t2(t1_id, t2_id)

You will probably need to add NOT NULL to the declarations for each of the two columns.

Alternatively, you could choose to forego the primary key column (if all you're using it for is uniqueness) and create the primary key on the combination of t1_id and t2_id:

CREATE TABLE t1t2(
t1_id integer NOT NULL,
t2_id integer NOT NULL,
PRIMARY KEY (t1_id, t2_id),
foreign key(t1_id) references t1(id),
foreign key(t2_id) references t2(id));

The PRIMARY KEY is a special case of a UNIQUE index. Using the composite PRIMARY KEY saves you one column and one index, but requires your application to know both t1_id and t2_id to retrieve a single row from the table.

Related questions

"Insert if not exists" statement in SQLite

I have an SQLite database. I am trying to insert values (users_id, lessoninfo_id) in table bookmarks, only if both do not exist before in a row. INSERT INTO bookmarks(users_id,lessoninfo_id) VALUES( (SELECT _id FROM Users …

Read More
Conditional SQLite check constraint?

I have a table defined by the following SQL: CREATE TABLE test ( id integer PRIMARY KEY NOT NULL UNIQUE, status text NOT NULL, enddate date, /* Checks */ CHECK (status IN ("Current", "Complete")) ); I'd like to add a constraint that requires enddate …

Read More
How do I DROP a constraint from a sqlite (3.6.21) table?

I have the following table: CREATE TABLE child( id INTEGER PRIMARY KEY, parent_id INTEGER CONSTRAINT parent_id REFERENCES parent(id), description TEXT); How do I drop the constraint?

Read More