SQLite composite key (2 foreign keys) Link table
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))
You'll probably want separate (single column) indexes on house_items.house_id
and house_items.item_id
as well.
Just to complement the first answer, it's a good practice to add a name to constraints, like the code below:
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));
There is no prohibition about a PRIMARY KEY not also being a FOREIGN KEY for those designs that require this kind of relation. Your problem isn't one of those, however, since the natural PRIMARY KEY in the linking table is a composite of the two columns, each a FOREIGN KEY back to one of the other tables.