Удаление родительской записи в PostgreSQL при удалении дочерней в случае наличия внешнего ключа из родительской записи к дочерней

Рейтинг: 0Ответов: 1Опубликовано: 18.07.2023

Друзья, всем привет. Столкнулись на работе с задачей, которую никак не получается решить. Стало очевидно, что простого решения не найти, поэтому решили спросить совета более опытных людей на форуме.

  1. У нас имеется БД PostgreSQL и мы работаем с ней с помощью python фреймворка SqlAlchemy, используя его ORM

  2. У нас есть сущность, которую можно представить в виде:

{   
    uuid: UUID
    data: {
        property_1: {
            value: str
        }
        property_2: {
            value: str
        }
        property_3: {
            value: str
        }
    }
}

или как несколько вложенных сущностей

Main
{   
    uuid: UUID
    data: Data
}


Data
{
    property_1: Property
    property_2: Property
    property_3: Property
}


Property
{
    value: str
}

  1. У нас имеются три таблицы, которые позволяют хранить такую сущность

main_table
{
    id: int
    uuid: UUID  
}


data_table
{
    id: int
    main_id: int = ForeignKey (main_table.id)
    property_1_id: ForeignKey (property_table.id)
    property_2_id: ForeignKey (property_table.id)
    property_3_id: ForeignKey (property_table.id)
}


property_table
{
    id: int
    value: str
}

Видно, что вторая таблица содержит несколько колонок одного и того же типа (Property)

Получается такая связь по внешним ключам между таблицами:

main_table <--- data_table ---> property_table

Если отравить сущность в базу, то она корректно разложится по таблицам, при чтении все также корректно собирается из таблиц в один объект.

НО есть проблема с удалением! Для ForeignKey в data_table установлено ON DELETE CASCADE, поэтому когда мы удаляем запись а main_table, то автоматически удаляется и запись в data_table, а записи в property_table остаются висеть мусором, потому что не они ссылаются на запись в data_table, а наоборот, поэтому тут каскадное удаление не работает.

Вопрос. С виду это достаточно типовая задача, но мы еще с таким не сталкивались. Прошу подсказать подход или подходы в решении. Нам необходимо иметь возможность удалять всю сущность, то есть записи во всех таблицах, связанных с сущностью. Другими словами, чтобы при удалении записи в main_table удалялись все записи, связанные с ней из таблиц data и property

Заранее спасибо!

postgresql sqlalchemy
Источник: Stack Overflow на русском

Ответы

▲ 0

Что бы обеспечить каскадное удаление связанных данных вам необходимо добавить внешний ключ с соотвествующими параметрами с отношением один ко многим. Вы создали множество внешних ключей по направлению от не уникальных полей таблицы свойств к уникальному полю таблицы значений. По большому счету такая схема не имеет никакого практического смысла. При данном подходе вам досточно двух таблиц при этом во второй таблице нужно добавить поле value. Обратите внимание что вы находитесь на половине пути между двумя разными подходами к формализации данных (не путать с нормализацией) данные могут быть формализованы на основе схемы данных или на основе данных. Две краности этих вариантов это формализация на основе данных EAV и формализация на основе схемы. Каждый вариант имеет свои плюсы и минусы. В общем случае формализация на основе данных дает гибкость в ущерб проиизводительности и добовляет сложности в оперировании данными. Формализация на основе схемы, да простят меня DBA, лишает гибкости но дает приимущества в производительности, компактности и простоте работы с данными. ИМХО по этому на сегодняшний день все так сильно уперлись в JSON решив что это панацея от всех бед, что лично у меня вызывает большие сомнения. Вы с вашей схемой застряли на пол пути от формализации на основе схемы к формализации на основе данных по большому счету не получив никаких приимуществ ни от одного из подходов. Вы заставляете все сущности получать статический набор свойств полагая что отсуствие значения для этих полей как то исправит ситуацию (не исправит и они прекрасно могут не иметь значения value=null в той же таблице) если это способ достичь вариативности сущностей, то он не сработает в таком виде. В общем случае для описания сущностей на основе данных я использую минимум четыре таблицы. Таблицу классов сущностей, таблицу свойств классов сущностей. Таблицу сущностей и таблицу значений свойств сущностей свзянную со своством класса сущности и с экземпляром сущности. Это уже не EAV но и не формализация на основе схемы такое решение сочетает досточное количество приимуществ от обоих подходов и обеспечивает независисмую вариативность сущностей при этом вы можете оперировать классами сущностей так как наборы их свсойств по сути формализованы вы например можете делать поиск как в ДНС по полям классов родственных сущностей сразу отбрасывая из результатов сущности чуждых классов.