Вставьте несколько значений с одним и тем же внешним ключом

У меня есть две таблицы, которые ссылаются друг на друга:

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, но это не рассматривает все INSERT как одну транзакцию. Другими словами, последний идентификатор продолжает увеличиваться.

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

Я хотел бы избежать использования SELECT в каждой новой строке. Есть ли способ вставить несколько значений в Item, ссылаясь на последнее известное room_id в Room?

Что-то вроде CROSS JOIN, вероятно, было бы очень полезно, но я не знаю, как заставить константы вести себя в этом случае.

Конечный результат, который я ищу, это чтобы Room выглядел так:

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

И Item вот так:

item_id | room_id | item_name
--------+---------+-----------
      1 |       1 | Chair
      2 |       1 | TV
      3 |       1 | Carpet
Формы c голосовым вводом в React с помощью Speechly
Формы c голосовым вводом в React с помощью Speechly
Пытались ли вы когда-нибудь заполнить веб-форму в области электронной коммерции, которая требует много кликов и выбора? Вас попросят заполнить дату,...
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Стилизация и валидация html-формы без использования JavaScript (только HTML/CSS)
Будучи разработчиком веб-приложений, легко впасть в заблуждение, считая, что приложение без JavaScript не имеет права на жизнь. Нам становится удобно...
Flatpickr: простой модуль календаря для вашего приложения на React
Flatpickr: простой модуль календаря для вашего приложения на React
Если вы ищете пакет для быстрой интеграции календаря с выбором даты в ваше приложения, то библиотека Flatpickr отлично справится с этой задачей....
В чем разница между Promise и Observable?
В чем разница между Promise и Observable?
Разберитесь в этом вопросе, и вы значительно повысите уровень своей компетенции.
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Что такое cURL в PHP? Встроенные функции и пример GET запроса
Клиент для URL-адресов, cURL, позволяет взаимодействовать с множеством различных серверов по множеству различных протоколов с синтаксисом URL.
Четыре эффективных способа центрирования блочных элементов в CSS
Четыре эффективных способа центрирования блочных элементов в CSS
У каждого из нас бывали случаи, когда нам нужно отцентрировать блочный элемент, но мы не знаем, как это сделать. Даже если мы реализуем какой-то...
0
0
17
1
Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Вы можете использовать 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';

Смотрите демо.

Я не знал, что вы можете сделать CTE из таких литералов с помощью VALUES. Это здорово. Спасибо.

Mad Physicist 10.04.2022 11:03

Или просто выберите литерал. Похоже, мне нужно вернуться к учебнику.

Mad Physicist 10.04.2022 11:05

Почему-то второе решение кажется мне более легким для чтения...

Mad Physicist 10.04.2022 19:17

Другие вопросы по теме

SQL: поиск максимального среднего, сгруппированного по идентификатору
Удалить с помощью внутреннего соединения с составным первичным ключом
Есть ли способ форматировать данные в списке в столбцы, когда данные из базы данных SQLITE
Проблема с заменой значений столбца базы данных NULL sqlite3 другими типами в Python 3?
Объект aiosqlite «Результат» не имеет атрибута «выполнить»
Почему я получаю ошибку SQLite.swift при вставке значений Int64?
Как выбрать все данные из одной таблицы и записи из другой таблицы, совпадающие с данными при первом выборе. Все в одном запросе
Обновление Android sqlite не обновляется должным образом, строковый литерал в двойных кавычках или такой столбец не возникает Ошибки
Выберите записи, которые не начинаются с «xxx», присоединенные к другой таблице.
React Native Running on Expo — ошибка SQLite при использовании предварительно заполненной базы данных — TypeError: undefined не является функцией (около '... db.transaction...')