如何更改“参考”？在PostgreSQL中？ [英] How to alter "REFERENCES" in PostgreSQL?

问题描述

表名更改后，如何更改对PostgreSQL中表的引用？

How can I alter the reference to a table in PostgreSQL when the table name has been changed?

说我有：

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
);

我后来做：

ALTER TABLE example1 RENAME TO example3; 

如何更改外键约束的定义？

How to change the definition of the foreign key constraint?

example1fk integer REFERENCES example1 (id) DEFERRABLE INITIALLY DEFERRED,

推荐答案

表和/或其他对象之间的内部依赖关系永远不会绑定到对象名称。在内部，每个对象都存储在目录表中，该对象的OID（内部主键）用于其他。

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 everything else.

外键引用存储在目录表 pg_constraint 和 pg_depend 。更改表名称完全不会削弱功能。

Accordingly, a FOREIGN KEY reference is stored in the catalog tables pg_constraint and pg_depend. Changing table names will not impair functionality at all.

约束的名称保持不变。您可以忽略它，也可以重命名约束，以免引起误解。

The name of the constraint remains unchanged. You can ignore that, or you may want to rename the constraint so it's not misleading.

但是，由于在创建时未指定约束名称，因此系统选择了默认情况下为 example2_example1fk_fkey ，除非已使用名称。没有引用引用的表名称。但是在您的示例中，列名也可能需要更改。

However, since you did not specify a constraint name at creation time, the system picked a default, which is example2_example1fk_fkey in your case unless the name was taken. No reference to the referenced table name. But the column name will likely have to change in your example, too. And that is used in the constraint name.

ALTER TABLE example2 RENAME example1fk TO example3fk;  -- rename column

在Postgres 9.2或更高版本中，您也可以重命名约束（需要评论的地方）：

In Postgres 9.2 or later you can just rename the constraint as well (as dequis commented):

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;

手册中的详细信息。

这篇关于如何更改“参考”？在PostgreSQL中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！