Кейс: изменение значения первичного ключа

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

Вопрос об изменении значения первичного ключа может также возникнуть в контексте интеграции систем.

Например, система А посылает в шину данных сообщения об изменениях бизнес-сущности Товар. И в этих сообщениях каждый отдельный экземпляр сущности – индивидуальный товар – идентифицируется бизнес-ключом, значение которого может быть изменено пользователем системы А (см. мой пост Кейс: изменение значения бизнес-ключа). А в системе Б, которая получает сообщения из шины данных и на их основании актуализирует собственную копию бизнес-сущности Товар, бизнес-ключ является первичным ключом таблицы в БД. И на него ссылаются внешние ключи из других таблиц.

Сможет ли система Б обновить первичный ключ, если она получит сообщение об изменении бизнес-ключа?

Внешний ключ в реляционных СУБД, таких как MySQL и PostreSQL, может быть объявлен автоматически обновляемым при изменении родительского ключа, на который он ссылается. Для этого нужно воспользоваться опцией on update cascade при объявлении ссылочного ограничения целостности.

-- PostgreSQL

create table parent (
    parent_id varchar(3) primary key
);

create table child (
    parent_id varchar(3) references parent (parent_id) on update cascade
);

insert into parent values ('AAA');
insert into child values ('AAA');

select * from parent p join child c on p.parent_id = c.parent_id
;
parent_id parent_id
--------- ---------
AAA       AAA

update parent set parent_id = 'AAB' where parent_id = 'AAA';

select * from parent p join child c on p.parent_id = c.parent_id
;
parent_id parent_id
--------- ---------
AAB       AAB

drop table child;
drop table parent;

А в СУДБ Oracle опция on update cascade на поддерживается. Для достижения результата, аналогичного тому, что производит on update casade, в СУБД Oracle нужно объявить ссылочные ограничения отложенными (initially deferred) до завершения транзакции и, после изменения родительского ключа, явно выполнить update'ы дочерних ключей в дочерних таблицах, после чего выполнить commit.

-- Oracle

create table parent (
    parent_id varchar2(3) primary key
);

create table child (
    parent_id varchar2(3) references parent (parent_id) initially deferred
);

insert into parent values ('AAA');
insert into child values ('AAA');

select * from parent p join child c on p.parent_id = c.parent_id
;
PARENT_ID PARENT_ID
--------- ---------
AAA       AAA

update parent set parent_id = 'AAB' where parent_id = 'AAA';

select * from parent p cross join child c
;
PARENT_ID PARENT_ID
--------- ---------
AAB       AAA

update child set parent_id = 'AAB' where parent_id = 'AAA';

select * from parent p join child c on p.parent_id = c.parent_id
;
PARENT_ID PARENT_ID
--------- ---------
AAB       AAB

-- validate foreign key constraint
commit;

drop table child;
drop table parent;

Это редкий случай, когда для реализации продакшн-решения мне кажется уместной опция on update cascade в SQL и ссылочное ограничение с отложенной проверкой целостности в СУБД Oracle.

Таким образом, технически изменение значения первичного ключа реализуемо.

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

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

В заключение – о таблицах, на которые нет ссылок в БД. Часто это таблицы, представляющие конфигурационные или транзакционные сущности предметной области. Например, таблица с ценами закупаемых товаров может иметь составной первичный ключ (код товара, код поставщика, дата начала действия цены) и при этом дата начала действия цены может изменяться, если это дата в будущем. Поскольку ссылок на таблицу нет, то упомянутые выше отягчающие обстоятельства отсутствуют. Но необходимо учитывать возможность изменения значения первичного ключа в сценарии интеграции систем, когда закупочные цены передаются из системы-источника в другие системы, – как это показано в посте Кейс: изменение значения бизнес-ключа.