SQLite复合主键（2个外键）连接表

Question

我已经阅读了SQLite创建表语句的BNF语法，该语法非常酷。

网址是：http://www.sqlite.org/lang_createtable.html

我想知道如何在这两个表之间创建一个链接表。

假设我有一张名为houses的表和另一张名为electrical_items的表。

我想要创建一个链接表来将house_id和item_id作为组合键，但我不确定如何去做，好像不允许主键成为外键?

N.B. 我想要一个第三个字段pap_tested，用于存储房子里的电器被pap_tested的日期，因此通过组合主键的链接表似乎是最好的方法。

- Luke

Answer 1

以下两种方法都适用于你的关联表：

create table house_items (
    house_id integer not null,
    item_id  integer not null,
    foreign key (house_id) references houses(id),
    foreign key (item_id) references electrical_items(id),
    primary key (house_id, item_id)
)

create table house_items (
    house_id integer not null references houses(id),
    item_id  integer not null references electrical_items(id),
    primary key (house_id, item_id)
)

你可能需要在house_items.house_id和house_items.item_id上建立单独的（单列）索引，详情请见这里。

- mu is too short

---

跟随链接后，我认为它不适用，因为house_id是house表的主键，而item_id是items表的主键。 - Luke

你好，在Android Studio SQLite3中，主键是（_id）“Columns.ID”，我仍然使用（id）还是改为（_id）？ - user5793598

@Ken，在这种情况下，你会使用 _id，语法是 foreign key (from_column_list) references to_table(to_column_list)。 - mu is too short

Answer 2

为了补充第一个答案，给约束条件添加名称是一种好的做法，例如下面的代码：

create table house_items (
    house_id integer not null,
    item_id  integer not null,
    constraint house_items_pk primary key (house_id, item_id),
    constraint house_items_house_fk foreign key (house_id) references houses(id),
    constraint house_items_items_fk foreign key (item_id) references electrical_items(id));

- Danilo Teodoro

Answer 3

对于那些需要这种关系的设计来说，主键也可以是外键，没有任何禁止。然而，你的问题并不属于这类问题，因为连接表中自然的主键是两个列的组合，每个列都是指向另一个表的外键。

- Larry Lustig

Answer 4

我创建了一个带有两个外键的表，并设置了on update cascade和on delete cascade选项。

CREATE TABLE category_subcategory
(
 category_subcategory_id INTEGER PRIMARY KEY,
 category_id             INTEGER NOT NULL,
 subcategory_id          INTEGER NOT NULL,
 FOREIGN KEY(category_id) REFERENCES categories(id) ON DELETE CASCADE ON
 UPDATE CASCADE,
 FOREIGN KEY(subcategory_id) REFERENCES subcategories(subcategory_id) ON
 DELETE CASCADE ON UPDATE CASCADE
 );

- Suleman Ilyas