SQLite复合键（2个外键）链接表 [英] SQLite composite key (2 foreign keys) Link table

问题描述

我在这里找到SQLite创建表语句

I've read the rather cool styled BNF grammar for the SQLite create table statement

，我读过相当酷的风格的BNF语法： http://www.sqlite.org/lang_createtable.html

found here: http://www.sqlite.org/lang_createtable.html

我想知道如何我会在这些

I was wondering how I'd go about creating a link table between these

之间创建一个链接表。我有一张表，让我们说，房子和另一个电气项目。

I have one table, lets say, houses, and another electrical_items.

我想创建一个链接表，将house_id和item_id作为一个复合键，但我不知道我该怎么做，它似乎不允许一个主键作为一个外键？

I want to create a link table to have the house_id and the item_id as a composite key, but I'm not sure how I'd go about doing it, it doesn't appear to allow a primary key to be a foreign key ?

NB我想要一个第三个字段pap_tested存储日期电气项目在房子是pap_tested，所以这个链接表通过复合主键似乎是最好的方法。

N.B I want a third field pap_tested which stores the date the electrical item in the house was pap_tested so this linking table via composite primary key seems the best approach.

推荐答案

这两种方法都适用于您的关联表：

Either of these should work for your association table:

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 。

You'll probably want separate (single column) indexes on house_items.house_id and house_items.item_id as well.

这篇关于SQLite复合键（2个外键）链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！