Пропуск изменения CTE и возврат существующего ключа вместо вставки нового (если значение уже существует)

#php #sql #postgresql #sql-insert #common-table-expression

#php #sql #postgresql #sql-вставка #common-table-expression

Вопрос:

Я пишу запрос в PostgreSQL, который я использую в своем php-коде, где пользователь должен иметь возможность добавлять участников в список участников, введя имя участников. Я использую модифицирующий CTE для этого, потому что ему необходимо обновить три таблицы в базе данных:

  • Person(Person_ID:Serial, Name: Varchar, … другая не относящаяся к делу информация, можно оставить null)
  • Персонал (Personnel_ID:Serial, Роль: Varchar, Person_ID:Int (FK)) FilmPeople(Personnel_ID (FK), Media_ID (FK))
  • Таким образом, актер как person находится в таблице person, затем подключается к роли в personal, а затем роль подключается к media_ID .

Запрос до сих пор:

 WITH person_cte AS (
     INSERT INTO Person(Name)
     SELECT :actorname
     WHERE NOT EXISTS (
         SELECT 1 FROM person WHERE name = :actorname2)
     RETURNING person_id
     ),
     personnel_cte AS (
     INSERT INTO Personnel(role, person_id)
     SELECT 'Actor', person_id FROM person_cte
     RETURNING personnel_id
     )
     INSERT INTO FilmPeople(Personnel_ID, Media_ID)
     SELECT personnel_id, :id from personnel_cte¨
 

Итак, проблема, как вы, ребята, вероятно, видите, заключается в том, что мой запрос пока работает только в том случае, если субъект вообще не существует в базе данных. Сбой, если он находит субъекта в таблице Person. На самом деле я хочу, чтобы, если актер существует лично, ничего не вставлял, а возвращал уже существующий person_id следующему cte. И затем то же самое, если персонал (роль) уже существует для этого актера, затем перейдите к personnel_id и добавьте актера только в последнюю таблицу, чтобы связать его / ее с фильмом. И даже здесь, если оно уже существует, ничего не делайте.

РЕДАКТИРОВАТЬ: так создаются таблицы на данный момент:

 CREATE TABLE Person(
Person_ID serial,
Name varchar(255),
DateOfBirth date,
Gender varchar(45),
CONSTRAINT PK_Person PRIMARY KEY (Person_ID)
);

CREATE TABLE Personnel(
Personnel_ID serial,
Role varchar(255),
Person_ID bigint,
CONSTRAINT PK_Personnel PRIMARY KEY (Personnel_ID),
CONSTRAINT FK_PersonPersonnel FOREIGN KEY (Person_ID)
REFERENCES Person(Person_ID)
);

CREATE TABLE FilmPeople(
Personnel_ID bigint,
Media_ID int,
CONSTRAINT PK_FilmPeople PRIMARY KEY (Personnel_ID, Media_ID),
CONSTRAINT FK_PersonnelFilmPeople FOREIGN KEY (Personnel_ID)
REFERENCES Personnel(Personnel_ID),
CONSTRAINT FK_MediaFilmPeople FOREIGN KEY (Media_ID)
REFERENCES Media(Media_ID)
);
 

Ответ №1:

Когда ничего не вставлено, returning предложение возвращается пустым — так что это не тот подход, который вам нужен здесь. Вместо этого я бы рекомендовал объединить таблицы. Чтобы это работало правильно, я бы настоятельно рекомендовал иметь уникальные ключи в каждой таблице, например:

 person(name)
personnel(role,person_id)
filmpeople(personnel_id, media_id)
 

Затем вы можете использовать on conflict :

 with 
    person_cte as (
        insert into person(name)
        values (:actorname)
        on conflict (name) do nothing
    ),
    personnel_cte as (
        insert into personnel(role, person_id)
        select 'actor', p.person_id 
        from person p
        where p.name = :actorname
        on conflict (role, person_id) do nothing
     )
insert into filmpeople(personnel_id, media_id)
select personnel_id, :id 
from pl.personnel pl
inner person p on p.person_id = pl.person_id
where p.name = :actorname and pl.role = 'actor'
on conflict (personnel_id, media_id) do nothing
 

Комментарии:

1. Спасибо! Я немного смущен первым абзацем об уникальных ключах, ваш пример точно такой, как таблицы, или я что-то упускаю? У Person есть Person_ID, у персонала есть Personnel_ID, а затем filmpeople объединяет персонал с медиа-ключом.

2. @SanjinRedzepagic: я имею в виду, что у вас должны быть уникальные ограничения, как описано в первом абзаце (возможно, это уже так). Например, в таблице filmpeople этот кортеж столбцов должен быть определен как unique : (personnel_id, media_id)

3. Прошу прощения за то, что я немного глуп, я добавил, как создаются таблицы на данный момент в исходном сообщении. Не могли бы вы взглянуть на это и сказать мне, какие изменения, по вашему мнению, необходимо внести?

4. Что предлагает @GMB, так это то, что вы добавляете уникальный бизнес-ключ к каждой таблице, затем используете этот ключ в каждом последующем cte или основном запросе, для таблицы Person вам нужно «Изменить таблицу добавить ограничение person_bk уникальное имя»; Аналогичная инструкция alter table применяется к другим таблицам.