两列唯一约束的组合

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

我创建了连接表t1和t2的表t1t2，如下所示：

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?

是否可以定义仅启用元组唯一值（t1_id，t2_id）的约束（限制）？或者我应该在应用程序中检查这个？

3 个解决方案

#1

 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.

您可能需要为两列中的每一列的声明添加NOT NULL。

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:

或者，您可以选择放弃主键列（如果您使用它的唯一性）并在t1_id和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.

PRIMARY KEY是UNIQUE索引的特例。使用复合PRIMARY KEY可以保存一列和一个索引，但需要您的应用程序知道t1_id和t2_id以从表中检索单行。

#2

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

您可以为create table语句添加唯一约束。这不一定是主键。

UNIQUE(t1_id, t2_id),

#3

You could create your UNIQUE primary index with those options to keep your primary key and a unique constraint SQL Lite New Index option

您可以使用这些选项创建UNIQUE主索引，以保留主键和唯一约束SQL Lite新索引选项

#1

 CREATE UNIQUE INDEX idx_twocols ON t1t2(t1_id, t2_id)