Создание двух записей и создание связи в рамках одной транзакции(SQL)

Question

Всем доброго времени суток. Начал более глубоко изучать язык SQL и реляционные базы данных, без использования различных ORM. И перед мной стал вопрос, можно ли в пределах одной транзакции создать две записи в разных таблицах, и связать их в третьей таблице (многие ко многим).

Вот пример 3-х простых таблиц:

CREATE TABLE IF NOT EXISTS author(
  id BIGSERIAL PRIMARY KEY UNIQUE,
  name VARCHAR(255) NOT null
);

CREATE TABLE IF NOT EXISTS books(
  id BIGSERIAL PRIMARY KEY UNIQUE,
  title VARCHAR(255) NOT null
);

CREATE TABLE IF NOT EXISTS author_book(
  id BIGSERIAL NOT null UNIQUE,
  author_id INTEGER NOT null,
  books_id INTEGER NOT null,
  FOREIGN KEY (author_id) REFERENCES author (id),
  FOREIGN KEY (books_id) REFERENCES books (id)
);

А теперь я хочу выполнить транзакцию, в которой создам запись author и запись books, а вот как мне в этой же транзакции связать таблицы в таблице author_book:

BEGIN;

INSERT
  INTO author(name)
  VALUES('Alexandr');

INSERT
  INTO books(title)
  VALUES('War and world');

COMMIT;

В том случае, если бы айдишник в author и books я ложил из вне, я бы мог создать запись author_book с этими айдишниками, но в моем примере айдишники генерируются... Как возможно это сделать? В качестве СУБД используется Postgresql.

Answer 1

INSERT INTO author_book (author_id, books_id)
SELECT author.id, books.id
FROM author
CROSS JOIN books
WHERE author.name = 'Alexandr'
  AND books.title = 'War and world';

Answer 2

Returning Data from Modified Rows

WITH
author_insert as (
  INSERT INTO author(name)
  VALUES('Alexandr')
  RETURNING id
),
book_insert as (
  INSERT INTO books(title)
  VALUES('War and world')
  RETURNING id
)
INSERT INTO author_book (author_id, books_id)
VALUES (
  (SELECT id FROM author_insert),
  (SELECT id FROM book_insert)
);

db-fiddle

БЛОГ НА HUSL

Создание двух записей и создание связи в рамках одной транзакции(SQL)

Ответы (2 шт):