Каскадное обновление для связанных объектов

На мой взгляд, нет смысла использовать NULL для представления логического значения. Семантика is_active предполагает, что единственными разумными значениями являются True и False. Кроме того, NULL мешает каскадным обновлениям.

Поэтому я не использую NULL.

Сначала создайте «родительскую» таблицу с первичным ключом и уникальным ограничением для первичного ключа и «is_active».

create table parent (
  p_id integer primary key,
  other_columns char(1) default 'x',
  is_active boolean not null default true,
  unique (p_id, is_deleted)
);

insert into parent (p_id) values
(1), (2), (3);

Создайте дочернюю таблицу со столбцом «is_active». Объявите ограничение внешнего ключа, ссылающееся на столбцы в уникальном ограничении родительской таблицы (последняя строка в операторе CREATE TABLE выше), и выполняйте каскадные обновления.

create table child (
  p_id integer not null,
  is_active boolean not null default true,
  foreign key (p_id, is_active) references parent (p_id, is_active) 
    on update cascade,
  some_other_key_col char(1) not null default '!',
  primary key (p_id, some_other_key_col)
);

insert into child (p_id, some_other_key_col) values
(1, 'a'), (1, 'b'), (2, 'a'), (2, 'c'), (2, 'd'), (3, '!');

Теперь вы можете установить для «родителя» значение false, и это будет каскадно распространяться на все ссылочные таблицы.

update parent 
set is_active = false 
where p_id = 1;

select *
from child
order by p_id;

p_id  is_active  some_other_key_col
--
1     f          a
1     f          b
2     t          a
2     t          c
2     t          d
3     t          !

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

Типы данных диапазона PostgreSQL особенно полезны для такого рода работы. Я использую диапазоны дат, но диапазоны временных меток работают так же.

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

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

create extension btree_gist; -- Necessary for the kind of exclusion
                             -- constraint below.

create table parent (
  p_id integer not null,
  other_columns char(1) not null default 'x',
  valid_from_to daterange not null,
  primary key (p_id, valid_from_to),
  -- No overlapping date ranges for a given value of p_id.
  exclude using gist (p_id with =, valid_from_to with &&)
);

create table child (
  p_id integer not null,
  valid_from_to daterange not null,
  foreign key (p_id, valid_from_to) references parent on update cascade,

  other_key_columns char(1) not null default 'x',
  primary key (p_id, valid_from_to, other_key_columns),

  other_columns char(1) not null default 'x'
);

Вставьте некоторые образцы данных. В PostgreSQL тип данных daterange имеет специальное значение «бесконечность». В данном контексте это означает, что строка со значением 1 для «parent». «p_id» действительна с «2015-01-01» до бесконечности.

insert into parent values 
(1, 'x', daterange('2015-01-01', 'infinity'));

insert into child values
(1, daterange('2015-01-01', 'infinity'), 'a', 'x'),
(1, daterange('2015-01-01', 'infinity'), 'b', 'y');

Этот запрос покажет вам соединенные строки.

select *
from parent p 
left join child c 
       on p.p_id = c.p_id 
      and p.valid_from_to = c.valid_from_to;

Чтобы сделать строку недействительной, обновите диапазон дат. Эта строка (ниже) была действительна с 01.01.2015 по 31.01.2015. То есть он был мягко удален 31 января 2015 года.

update parent
set valid_from_to = daterange('2015-01-01', '2015-01-31')
where p_id = 1 and valid_from_to = daterange('2015-01-01', 'infinity');

Вставьте новую допустимую строку для p_id 1 и выберите дочерние строки, которые были признаны недействительными 31 января.

insert into parent values (1, 'r', daterange(current_date, 'infinity'));

update child set valid_from_to = daterange(current_date, 'infinity')
where p_id = 1 and valid_from_to = daterange('2015-01-01', '2015-01-31');

Основополагающая книга Ричарда Т. Снодграсса Разработка приложений баз данных, ориентированных на время, на SQL доступна бесплатно по адресу веб-страница его университета.

Вы можете использовать trigger :

CREATE OR REPLACE FUNCTION trg_upaft_upd_trip()
  RETURNS TRIGGER AS
$func$
BEGIN

UPDATE submission s
SET    is_active = NULL
WHERE  s.assessment_id = NEW.assessment_id
AND    NEW.is_active IS NULL;  -- recheck to be sure

RETURN NEW;                    -- call this BEFORE UPDATE

END
$func$  LANGUAGE plpgsql;

CREATE TRIGGER upaft_upd_trip
BEFORE UPDATE ON assessment
FOR EACH ROW
WHEN (OLD.is_active AND NEW.is_active IS NULL)
EXECUTE PROCEDURE trg_upaft_upd_trip();

Связанный:

• Как сделать Я создаю триггер для обновления столбца в другой таблице?

Имейте в виду, что триггер имеет больше возможных точек отказа, чем ограничения FK с ON UPDATE CASCADE ON DELETE CASCADE.

@Mike добавил решение с ограничением FK на несколько столбцов, которое я бы рассматривал как альтернативу.

Связанный ответ на dba.SE:

• Применение ограничений "через две таблицы"

Связанный ответ через неделю:

• Ограничения кросс-таблицы в PostgreSQL

Это скорее схематическая проблема, чем процедурная.

Возможно, вы уклонились от создания четкого определения того, «что представляет собой запись». На данный момент у вас есть объект A, на который может ссылаться объект B, и когда A "удален" (столбец is_active имеет значение FALSE или NULL в вашем текущем случае), B не отражает этого. Похоже, что это одна таблица (вы упоминаете только строки, а не отдельные классы или таблицы...), и у вас есть иерархическая модель, сформированная путем ссылки на себя. Если это так, вы можете думать о проблеме несколькими способами:

Рекурсивное происхождение

В этой модели у вас есть одна таблица, которая содержит все данные в одном месте, будь то родительская, дочерняя и т. д., и вы проверяете таблицу на наличие рекурсивных ссылок для обхода дерева.

Сложно сделать это должным образом в ORM, в котором отсутствует явная поддержка этого, без случайного написания подпрограмм, которые либо:

• итеративно выкапывать все дерьмо из вашей БД, выполнив хотя бы один запрос на узел или
• вытягивание всей таблицы сразу и обход ее в коде приложения

Однако это легко сделать в Postgres и позволить Django получить к нему доступ через модель через неуправляемое представление в создаваемом вами запросе о происхождении. (Я немного писал об этом однажды.) В этой модели ваш запрос будет спускаться по дереву до тех пор, пока не встретится с первая строка текущей ветки, которая помечается как неактивная и останавливается, таким образом эффективно усекая все строки ниже, связанные с этой (нет необходимости распространять столбец is_active!).

Если бы это была, скажем, запись в блоге + комментарии в одной и той же структуре (довольно распространенная схема CMS), тогда любая строка, которая является своим собственным родителем, является первичной сущностью, а все, что имеет родителя, который не является самим собой, является комментарием. Чтобы удалить всю запись в блоге + ее дочерние элементы, вы помечаете только строку записи в блоге как неактивную; чтобы удалить нить в комментариях, пометьте как неактивный комментарий, который начинает эту цепочку.

Для функции «блог + комментарии» это обычно самый простой способ сделать что-то — хотя большинство систем CMS ошибаются (но обычно только в тех случаях, которые имеют значение, если вы начнете серьезно работать с данными позже, если вы просто настраиваете какое-то место, где люди могут поспорить в Интернете, а затем чем хуже, тем лучше).

Рекурсивная родословная + внешнее определение "записи"

В этой модели у вас есть дерево узлов и ваши первичные объекты. Основные объекты помечаются как активные или нет, и этот атрибут является общим для всех связанных с ним элементов в контексте этого основного объекта (они существуют и имеют независимое от него значение) . Это означает две таблицы, одну для первичных сущностей и одну для вашего дерева узлов.

Используйте это, когда у вас происходит что-то более интересное, чем просто тематическое обсуждение. Например, модель компонентов, в которой дерево вещей может быть объединено отдельно в другие более крупные объекты, и вам нужен способ пометить эти «другие более крупные объекты» как активные или неактивные независимо от самих компонентов.

Дальше по кроличьей норе...

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

Подумайте о том, чтобы двигаться в этом направлении, если ваши данные более сложны. Если вы действительно нуждаетесь в моделях, настолько разложенных («нормализованных»), то я хотел бы предостеречь, что любой ORM, вероятно, принесет гораздо больше проблем, чем его ценность - вы начнете с головой сталкиваться с проблемой, что ORM фундаментально дырявые абстракции (1 объект никогда не может на самом деле равняться 1 таблице...).