用相同的外键插入多个值

Question

我有两个互相参考的表格：

CREATE TABLE Room
    room_id INTEGER PRIMARY KEY,
    room_name TEXT UNIQUE NOT NULL;

CREATE TABLE Item
    item_id INTEGER PRIMARY KEY,
    room_id INTEGER,
    item_name TEXT,
    FOREIGN KEY (room_id) REFERENCES Room (room_id) ON UPDATE RESTRICT ON DELETE RESTRICT;

现在我想添加一个新房间，并添加几十个项目以进入其中。

INSERT INTO Room(room_name) VALUES ('Living Room');

假设我不知道有多少个房间，我只想把东西放入客厅。为此，我需要选择正确的room_id。对于一个项目，这还不错：

INSERT INTO Item(room_id, item_name)
    SELECT room_id, 'Couch' AS item_name FROM Room WHERE room_name = 'Living Room';

但是，如果我想同时插入一堆值该怎么办。我尝试使用last_insert_rowid，但这不会将整个插入视为单个交易。换句话说，最后一个ID一直在增加，

INSERT INTO Item (room_id, item_name)
  VALUES
    (last_insert_rowid(), 'Chair'),
    (last_insert_rowid(), 'TV'),
    (last_insert_rowid(), 'Carpet');

我想避免在每个新行上使用选择。是否有一种方法可以将多个值插入item，同时引用Room中的最后一个已知ROOM_ID？

交叉加入的性质中的某些内容可能非常有用，但是我不知道如何使常数在这种情况下以

最终结果为例。 看起来像这样：

room_id | room_name
--------+-----------
      1 | Living Room

item这样：

item_id | room_id | item_name
--------+---------+-----------
      1 |       1 | Chair
      2 |       1 | TV
      3 |       1 | Carpet

Answer 1

您可以使用从新房间获得的 id 的 CROSS 连接到返回要插入的项目的 CTE：

WITH cte(item_name) AS (VALUES ('Chair'), ('TV'), ('Carpet'))
INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r CROSS JOIN cte c
WHERE r.room_name = 'Living Room';

请参阅 演示。

如果您使用的 SQLite 版本不支持 CTE，请在子查询中使用 UNION ALL：

INSERT INTO Item (room_id, item_name)
SELECT r.room_id, c.item_name
FROM Room r 
CROSS JOIN (
  SELECT 'Chair' item_name UNION ALL 
  SELECT 'TV' UNION ALL
  SELECT 'Carpet'
) c
WHERE r.room_name = 'Living Room';

请参阅 演示。