Используйте триггеры на унаследованных таблицах для замены внешних ключей

Question

Я новичок в PostgreSQL. У меня есть таблицы, такие как:

CREATE TABLE Person (
  ID SERIAL PRIMARY KEY,
  Name VARCHAR(32) NOT NULL DEFAULT '',
  Surname VARCHAR(32) NOT NULL DEFAULT '',
  Birthday DATE,
  Gender VARCHAR(8)
);

-- Student table inherits from person
CREATE TABLE Student (
  ID_Student SERIAL PRIMARY KEY,
  MajorDept VARCHAR(32),
) INHERITS(Person);

-- Student table inherits from person
CREATE TABLE Employee (
  ID_Employee SERIAL PRIMARY KEY,
  Position VARCHAR(32),
  Rank VARCHAR(32),
  Salary NUMERIC(12,2)
) INHERITS(Person);

-- Address table references person
CREATE TABLE Address (
  ID_Address SERIAL PRIMARY KEY,
  Person_id INTEGER REFERENCES Person(ID) NOT NULL,
  Email VARCHAR(32) UNIQUE,
  Country VARCHAR(32),
  CityCode INTEGER,
  City VARCHAR(32),
  AddressLine VARCHAR(60),
);

Согласно этим таблицам, когда я хочу вставить данные в Adress Таблица, Postgres дает эту ошибку:

ОШИБКА: вставка или обновление таблицы "адрес" нарушает ограничение внешнего ключа "address_person_id_fkey" ПОДРОБНЕЕ: Ключ (person_id)=(1) отсутствует в таблице "человек".

Я узнал это в Postgres

Индексы (включая ограничения уникальности) и ограничения внешнего ключа применяются только к отдельным таблицам, а не к их дочерним элементам наследования.

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

Вставив несколько строк в дочерние таблицы, я вижу данные с помощью команды "SELECT * FROM Person;" также. Это выглядит как:

Персональный стол

1;"Bill";"Smith";"1985-05-10";"male"
2;"Jenny";"Brown";"1986-08-12";"female"
3;"Bob";"Morgan";"1986-06-11";"male"
4;"Katniss";"Everdeen";"1970-08-12";"female"
5;"Peter";"Everdeen";"1968-08-12";"male"

Студенческий стол

1;"Bill";"Smith";"1985-05-10";"male";1;"chemistry"
2;"Jenny";"Brown";"1986-08-12";"female";2;"physics"
3;"Bob";"Morgan";"1986-06-11";"male";3;"physics"

Стол сотрудника

4;"Katniss";"Everdeen";"1970-08-12";"female";1;"Prof";"1";3500.00
5;"Peter";"Everdeen";"1968-08-12";"male";2;"Assist-Prof";"5";1800.00

Answer 1

Сначала избавьтесь от ФК примерно так:

alter table address drop constraint address_person_id_fkey

Если это жалуется на отсутствие address_person_id_fkey ограничение затем использовать \d address; в psql чтобы узнать, как называется FK.

Тогда такой простой триггер должен сделать свое дело:

create or replace function pseudo_fk_for_address() returns trigger as $$
begin
    if not exists(select 1 from person where id = new.person_id) then
        raise exception 'No such person: %', new.person_id;
    end if;
    return new;
end;
$$ language plpgsql;

И прикрепите это так:

create trigger pseudo_fk_for_address_trigger before insert or update on address 
for each row execute procedure pseudo_fk_for_address();

Тогда вы получите такую ​​ошибку, если попытаетесь добавить адрес для кого-то, кого нет в person (включая таблицы, которые наследуют от него):

playpen=> insert into address (person_id, email, country, citycode, city, addressline) values (3, 'ab', 'b', 2, 'c', 'd');
ERROR:  No such person: 3

Вы хотели бы добавить триггер ДО УДАЛИТЬ person чтобы избежать висящих ссылок, эта базовая структура была бы почти такой же. Вы могли бы хотеть индекс на address.person_id чтобы помочь поддержать триггер BEFORE DELETE.

Рекомендации:

• PL / PgSQL
• Процедуры запуска PL/pgSQL
• RAISE

Answer 2

Внешние ключи не наследуются. Если внешний ключ указывает на таблицу person то же значение должно быть в этой таблице. Реализация наследования ограничена в PostgreSQL, я процитирую из главы "Предостережения" в руководстве:

Нет хорошего обходного пути для этого случая.

Это включает в себя предложенный триггер @ Му. Вам нужно гораздо больше, чем триггер ON INSERT гарантировать ссылочную целостность. Я бы не стал это пробовать. Что произойдет, если вы удалите человека? Изменить ID?

Я бы посоветовал вообще не использовать наследование. Если вы все еще хотите или должны, я бы предложил несколько изменений в вашей модели данных.

• 1) email Не должно быть в таблице адресов, это не имеет ничего общего с адресом и все с человеком. Переместите это к столу person, Причиной смещения может быть то, что вы хотите обеспечить уникальность. Еще одна причина вообще не использовать наследование.

• 2) Колонны id_student а также id_employee избыточны. Используйте унаследованный столбец id в качестве первичных ключей вместо. Просто добавьте ограничение к вашим дочерним таблицам:

  CONSTRAINT student_pkey PRIMARY KEY (id)
  CONSTRAINT employee_pkey PRIMARY KEY (id)
  

  Это также устраняет один из двух источников возможных дубликатов в id столбец над деревом наследования. (Другой является то, что вы все еще можете вводить идентификаторы в student которые присутствуют в employee или же person, Еще одна оговорка в системе наследования. Поэтому никогда не вставляйте и не меняйте вручную id, Оставьте это для столбца по умолчанию и последовательности.

• 3) "Естественная" модель будет иметь отношение n:m между address а также person, Для вашей модели я бы реализовал это с помощью дополнительной таблицы person_address где таблица адресов address_id address а также person_id только мечты об ограничении внешнего ключа (исходная проблема).

  На ваш взгляд, адрес никогда не может быть заселен более чем одним человеком. Может быть, это достаточно хорошо для вашей цели. Таким образом, вы также можете вставить весь адрес в таблицу person (и унаследовать его от ученика и сотрудника), чтобы полностью избежать проблемы с внешним ключом.