postgresql 生成SQL更新主键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18271622/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Generate SQL to update primary key
提问by guettli
I want to change a primary key and all table rows which reference to this value.
我想更改引用此值的主键和所有表行。
# table master
master_id|name
===============
foo|bar
# table detail
detail_id|master_id|name
========================
1234|foo|blu
If I give a script or function
如果我给出一个脚本或函数
table=master, value-old=foo, value-new=abc
I want to create a SQL snippet that executes updates on all tables which refere to table "master":
我想创建一个 SQL 片段，它对引用表“master”的所有表执行更新：
update detail set master_id=value-new where master_id=value-new;
.....
With the help of introspection, this should be possible.
在自省的帮助下，这应该是可能的。
I use postgres.
我使用 postgres。
Update
更新
The problem is, that there are many tables which have a foreign-key to the table "master". I want a way to automatically update all tables which have a foreign-key to master table.
问题是，有很多表都有表“master”的外键。我想要一种方法来自动更新所有具有主表外键的表。
采纳答案by Lukasz Szozda
If you need to change PK you could use DEFFERED CONSTRAINTS:
如果您需要更改 PK，您可以使用DEFFERED CONSTRAINTS：
SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit.Each constraint has its own IMMEDIATE or DEFERRED mode.
SET CONSTRAINTS 设置当前事务中的约束检查行为。在每个语句的末尾检查 IMMEDIATE 约束。在事务提交之前不会检查 DEFERRED 约束。每个约束都有自己的 IMMEDIATE 或 DEFERRED 模式。
Data preparation:
数据准备：
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');
In normal situtation if you try to change master detail you will end up with error:
在正常情况下，如果您尝试更改主详细信息，您最终会出现错误：
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".
But if you change FK resolution to deffered, there is no problem:
但是如果你把FK分辨率改成defered，就没有问题了：
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;
Please note that you could do many things inside transaction, but during COMMIT?all referential integrity checks have to hold.
请注意，您可以在事务内部做很多事情，但在COMMIT所有引用完整性检查期间都必须保持。
EDIT
编辑
If you want to automate this process you could use dynamic SQL and metadata tables. Here Proof of Concept for one FK column:
如果你想自动化这个过程，你可以使用动态 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');
And code:
和代码：
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;
EDIT 2:
编辑2：
I tried it, but it does not work. It would be nice, if the script could show the SQL. This is enough. After looking at the generated SQL I can execute it if psql -f
have you tried it? It did not work for me.
我试过了，但它不起作用。如果脚本可以显示 SQL，那就太好了。这就够了。查看生成的 SQL 后，如果 psql -f，我可以执行它
你试过了吗？它对我不起作用。
Yes, I have tried it. Just check above live demo links. I prepare the same demo with more debug info:
是的，我试过了。只需检查上面的实时演示链接。我准备了更多调试信息的相同演示：
- values before
- executed SQL
- values after
- 之前的值
- 执行的 SQL
- 之后的值
Please make sure that FKs are defined as DEFFERED.
请确保 FK 被定义为 DEFFERED。
LAST EDIT
上次编辑
Then I wanted to see the sql instead of executing it. I removed "perform" from your fiddle, but then I get an error. See: http://dbfiddle.uk/?rdbms=postgres_10&fiddle=b9431c8608e54b4c42b5dbd145aa1458
然后我想查看sql而不是执行它。我从您的小提琴中删除了“执行”，但随后出现错误。参见：http: //dbfiddle.uk/?rdbms=postgres_10&fiddle=b9431c8608e54b4c42b5dbd145aa1458
If you only want to get SQL code you could create function:
如果您只想获取 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;
And execution:
和执行：
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';
Of course there is a place for improvement for example handling identifiers like "table with space in name" and so on.
当然，还有改进的地方，例如处理诸如“名称中有空格的表”等标识符。
回答by Craig Ringer
The easiest way to deal with primary key changes - by far - is to ALTERyour referring foreign key constraints to be ON UPDATE CASCADE.
到目前为止，处理主键更改的最简单方法是将ALTER引用的外键约束设为ON UPDATE CASCADE.
You are then free to update the primary key values, and the changes will cascade to child tables. It can be a very slow process due to all the random I/O, but it will work.
然后您可以自由更新主键值，更改将级联到子表。由于所有随机 I/O，这可能是一个非常缓慢的过程，但它会起作用。
You do need to watch out not to violate uniqueness constraints on the primary key column during the process.
在此过程中，您需要注意不要违反主键列的唯一性约束。
A fiddlier but faster way is to add a new UNIQUEcolumn for the new PK, populate it, add new columns to all the referring tables that point to the new PK, drop the old FK constraints and columns, then finally drop the old PK.
一种更繁琐但更快的方法是UNIQUE为新 PK添加一个新列，填充它，向所有指向新 PK 的引用表添加新列，删除旧的 FK 约束和列，然后最后删除旧的 PK。
回答by Rahul Tripathi
I dont think you can update the Primary key. One possible work around is that you can remove the primary key constraint from the table column. Then update the column value.
我不认为你可以更新主键。一种可能的解决方法是您可以从表列中删除主键约束。然后更新列值。
Updating the primary key can lead you to some serious problems. But if you still want to do it.
更新主键可能会导致一些严重的问题。但是如果你还想做的话。
Please refer this Thread.(kevchadders has given a solution.)
请参考这个线程。（kevchadders 已经给出了解决方案。）
回答by guettli
I found a dirty solution: in psqlthe command \d master_tableshow the relevant information. With some text magic, it is possible to extract the needed information:
我找到了一个肮脏的解决方案：在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 set ='NEW' WHERE ='OLD';" \
tmp/foreign-keys.txt
psql -1 -f tmp/foreign-keys.txt
Result:
结果：
UPDATE "master_table" SET id='NEW' WHERE id='OLD';
UPDATE "other_table" SET master_id='NEW' WHERE master_id='OLD';
...
But better solutions are welcome.
但欢迎更好的解决方案。
