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

Я хочу создать фрагмент SQL, который выполняет обновления для всех таблиц, которые относятся к таблице "master":

С помощью интроспекции это должно быть возможно.

Проблема заключается в том, что существует множество таблиц, которые имеют внешний ключ к таблице "master". Я хочу, чтобы автоматически обновлять все таблицы, которые имеют внешний ключ для мастер-таблицы.

Ответы

Ответ 1

Если вам нужно изменить PK, вы можете использовать DEFFERED CONSTRAINTS:

SET CONSTRAINTS задает поведение проверки ограничений в текущей транзакции. Ограничения IMMEDIATE проверяются в конце каждого оператора. Ограничения DEFERRED не проверяются до фиксации транзакции. Каждое ограничение имеет свой собственный режим IMMEDIATE или DEFERRED.

Подготовка данных:

CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY, name VARCHAR(10));
INSERT INTO master(master_id, name) VALUES ('foo', 'bar');

CREATE TABLE detail(detail_id INT PRIMARY KEY, master_id VARCHAR(10)
   ,name VARCHAR(10)
   ,CONSTRAINT  fk_det_mas FOREIGN KEY (master_id) REFERENCES master(master_id));

INSERT INTO detail(detail_id, master_id, name) VALUES (1234,'foo','blu');

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

update detail set master_id='foo2' where master_id='foo';
-- ERROR:  insert or update on table "detail" violates foreign key 
-- constraint "fk_det_mas"
-- DETAIL:  Key (master_id)=(foo2) is not present in table "master"

update master set master_id='foo2' where master_id='foo';
-- ERROR:  update or delete on table "master" violates foreign key
-- constraint "fk_det_mas" on table "detail"
-- DETAIL:  Key (master_id)=(foo) is still referenced from table "detail".

Но если вы измените разрешение FK на deffered, нет проблем:

ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id) 
REFERENCES master(master_id) DEFERRABLE;

BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master set master_id='foo2' where master_id = 'foo';
UPDATE detail set master_id='foo2' where master_id = 'foo';
COMMIT;

DBFiddle Demo

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

ИЗМЕНИТЬ

Если вы хотите автоматизировать этот процесс, вы можете использовать динамические таблицы SQL и метаданных. Здесь Доказательство концепции для одного столбца FK:

CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY, name VARCHAR(10));
INSERT INTO master(master_id, name)
VALUES ('foo', 'bar');

CREATE TABLE detail(detail_id INT PRIMARY KEY, master_id VARCHAR(10),
   name VARCHAR(10)
  ,CONSTRAINT  fk_det_mas FOREIGN KEY (master_id) 
   REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail(detail_id, master_id, name) VALUES (1234,'foo','blu');

CREATE TABLE detail_second(detail_id INT PRIMARY KEY, name VARCHAR(10),
   master_id_second_name VARCHAR(10)
  ,CONSTRAINT  fk_det_mas_2 FOREIGN KEY (master_id_second_name) 
   REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail_second(detail_id, master_id_second_name, name) 
VALUES (1234,'foo','blu');

И код:

BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
DO $$
DECLARE
   old_pk TEXT = 'foo';
   new_pk TEXT = 'foo2';
   table_name TEXT = 'master';
BEGIN
-- update childs
EXECUTE (select 
         string_agg(FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'' ;'
            ,c.relname,pa.attname, new_pk,pa.attname, old_pk),CHR(13)) AS sql
         from  pg_constraint pc
         join pg_class c on pc.conrelid = c.oid
         join pg_attribute pa ON pc.conkey[1] = pa.attnum 
          and pa.attrelid = pc.conrelid
         join pg_attribute pa2 ON pc.confkey[1] = pa2.attnum 
          and pa2.attrelid = table_name::regclass
         where pc.contype = 'f');

-- update parent        
EXECUTE ( SELECT FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'';'
         ,c.relname,pa.attname, new_pk,pa.attname, old_pk)
 FROM pg_constraint pc
 join pg_class c on pc.conrelid = c.oid
 join pg_attribute pa ON pc.conkey[1] = pa.attnum 
  and pa.attrelid = pc.conrelid
 WHERE pc.contype IN ('p','u')
   AND conrelid = table_name::regclass
);       

END
$$;
COMMIT;

DBFiddle Demo 2

ИЗМЕНИТЬ 2:

Я попробовал, но это не сработает. Было бы неплохо, если бы script мог показать SQL. Достаточно. Посмотрев на сгенерированный SQL, я могу выполнить его, если psql -f

Вы пробовали? Это не сработало для меня.

Да, я попробовал. Просто проверьте выше живые демонстрационные ссылки. Я готовлю одно и то же демо с дополнительной информацией об отладке:

значения перед
выполнил SQL
значения после

Убедитесь, что FK определены как DEFFERED.

DBFiddle 2 с информацией об отладке

ПОСЛЕДНИЙ РЕДАКТИРОВАТЬ

Затем мне захотелось увидеть sql вместо его выполнения. Я удалил "выполнить" из вашей скрипки, но потом я получаю сообщение об ошибке. См.: http://dbfiddle.uk/?rdbms=postgres_10&fiddle=b9431c8608e54b4c42b5dbd145aa1458

Если вы хотите получить код SQL, вы можете создать функцию:

CREATE FUNCTION generate_update_sql(table_name VARCHAR(100), old_pk VARCHAR(100), new_pk VARCHAR(100))
RETURNS TEXT 
AS 
$$
BEGIN
RETURN 
-- update childs
(SELECT 
         string_agg(FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'' ;',  c.relname,pa.attname, new_pk,pa.attname, old_pk),CHR(13)) AS sql
         FROM  pg_constraint pc
         JOIN pg_class c on pc.conrelid = c.oid
         JOIN pg_attribute pa ON pc.conkey[1] = pa.attnum and pa.attrelid = pc.conrelid
         JOIN pg_attribute pa2 ON pc.confkey[1] = pa2.attnum and pa2.attrelid = table_name::regclass
         WHERE pc.contype = 'f') || CHR(13) ||
-- update parent        
(SELECT FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'';',  c.relname,pa.attname, new_pk,pa.attname, old_pk)
 FROM pg_constraint pc
 JOIN pg_class c on pc.conrelid = c.oid
 JOIN pg_attribute pa ON pc.conkey[1] = pa.attnum and pa.attrelid = pc.conrelid
 WHERE pc.contype IN ('p','u')
   AND conrelid = table_name::regclass)
;       
END
$$ LANGUAGE  plpgsql;

И выполнение:

SELECT generate_update_sql('master', 'foo', 'foo');

UPDATE detail SET master_id = 'foo' WHERE master_id ='foo' ;
UPDATE detail_second SET master_id_second_name = 'foo' 
 WHERE master_id_second_name ='foo' ; 
UPDATE master SET master_id = 'foo' WHERE master_id ='foo';

Демонстрация функции DBFiddle

Конечно, есть место для улучшения, например, для обработки идентификаторов, таких как "таблица с пространством по имени" и т.д.

Ответ 2

Самый простой способ справиться с изменениями первичного ключа - безусловно, - это ALTER ваши ограничения внешнего ключа для ссылок ON UPDATE CASCADE.

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

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

Более быстрый, но более быстрый способ - добавить новый столбец UNIQUE для нового PK, заполнить его, добавить новые столбцы во все ссылочные таблицы, которые указывают на новый PK, удалить старые ограничения FK и столбцы, а затем, наконец, отбросить старый ПК.

Ответ 3

Я не думаю, что вы можете обновить ключ Primary. Одна из возможных проблем заключается в том, что вы можете удалить ограничение первичного ключа из столбца таблицы. Затем обновите значение столбца.

Обновление первичного ключа может привести к серьезным проблемам. Но если вы все еще хотите это сделать.

Пожалуйста, обратитесь к Thread. (kevchadders дал решение.)

Ответ 4

Я нашел грязное решение: в psql команда \d master_table показывает соответствующую информацию. С помощью некоторой текстовой магии можно извлечь необходимую информацию:

echo "UPDATE master_table SET id='NEW' WHERE id='OLD';" > tmp/foreign-keys.txt

psql -c '\d master_table' | grep -P 'TABLE.*CONSTRAINT.*FOREIGN KEY'  \
                                 >> tmp/foreign-keys.txt

reprec '.*TABLE ("[^"]*") CONSTRAINT[^(]*\(([^)]*)\).*' \
        "UPDATE \1 set \2='NEW' WHERE \2='OLD';" \
         tmp/foreign-keys.txt 

psql -1 -f tmp/foreign-keys.txt

Результат:

UPDATE "master_table" SET id='NEW' WHERE id='OLD';
UPDATE "other_table" SET master_id='NEW' WHERE master_id='OLD';
...

Но лучшие решения приветствуются.

Генерировать SQL для обновления первичного ключа