如何更改 PostgreSQL 中的“REFERENCES”？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/31414462/
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
How to alter "REFERENCES" in PostgreSQL?
提问by Jose Luis de la Rosa
How can I alter the reference to a table in PostgreSQL when the table name has been changed?
当表名已更改时，如何更改对 PostgreSQL 中表的引用？
Say I have:
说我有：
CREATE TABLE example1 (
id serial NOT NULL PRIMARY KEY,
name varchar(100)
);
CREATE TABLE example2 (
id serial NOT NULL PRIMARY KEY,
example1fk integer REFERENCES example1 (id) DEFERRABLE INITIALLY DEFERRED
);
Later I do:
后来我这样做：
ALTER TABLE example1 RENAME TO example3;
How to change the definition of the foreign key constraint?
如何更改外键约束的定义？
example1fk integer REFERENCES example1 (id) DEFERRABLE INITIALLY DEFERRED,
回答by Erwin Brandstetter
Internal dependencies between tables and / or other objects are never bound to the object name. Internally, every object is stored in catalog tables and the OID (internal primary key) of the object is used for everythingelse.
表和/或其他对象之间的内部依赖关系永远不会绑定到对象名称。在内部，每个对象都存储在目录表中，对象的 OID（内部主键）用于其他所有内容。
Accordingly, a FOREIGN KEYreference is stored in the catalog tables pg_constraintand pg_depend. Changing table names will not impair functionality at all.
因此，FOREIGN KEY引用存储在目录表pg_constraint和 中pg_depend。更改表名根本不会影响功能。
The nameof the constraint remains unchanged. You can ignore that, or you may want to rename the constraint so it's not misleading.
约束的名称保持不变。您可以忽略它，或者您可能想要重命名约束以免误导。
However, since you did not specify a constraint name at creation time, the system picked a default, which is example2_example1fk_fkeyin your case unless the name was taken. No reference to the referenced tablename. But the column namewill likely have to change in your example, too. And thatis used in the constraint name.
但是，由于您在创建时没有指定约束名称，因此系统选择了一个默认值，example2_example1fk_fkey除非采用该名称，否则就是您的情况。没有引用被引用的表名。但是在您的示例中，列名也可能必须更改。而这是在约束条件名称中使用。
ALTER TABLE example2 RENAME example1fk TO example3fk; -- rename column
In Postgres 9.2 or later you can just rename the constraint as well (as dequis commented):
在 Postgres 9.2 或更高版本中，您也可以重命名约束（如dequis 评论的那样）：
ALTER TABLE example2 RENAME CONSTRAINT example2_example1fk_fkey TO example2_example3fk_fkey;
In older versions, you have to drop and recreate the constraint to rename it, best in a single statement:
在旧版本中，您必须删除并重新创建约束以重命名它，最好在单个语句中：
ALTER TABLE example2 -- rename constraint
DROP CONSTRAINT example2_example1fk_fkey
, ADD CONSTRAINT example2_example3fk_fkey FOREIGN KEY (example3fk)
REFERENCES example3 (id) DEFERRABLE INITIALLY DEFERRED;
