Combination of a unique restriction of two columns

Question

I created table t1t2, which joins 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 (constraint) that allows only unique tuple values ​​(t1_id, t2_id)? Or should I check this in the app?

Answer 1

  CREATE UNIQUE INDEX idx_twocols ON t1t2(t1_id, t2_id) 

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

Alternatively, you can discard the primary key column (if you all use it for uniqueness) and create a primary key in a 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)); 

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

Answer 2

You can add a unique constraint to the create table statement. This should not be a primary key.

 UNIQUE(t1_id, t2_id), 

Answer 3

You can create your UNIQUE primary index with these parameters to preserve your primary key and unique SQL Lite constraint. New index parameter