如何在 psql 中使用外键约束将一个表的结构复制到另一个表? [英] How to copy structure of one table to another with foreign key constraints in psql?

问题描述

使用时不复制外键约束

create table table_name ( like source_table INCLUDING ALL)' 

在 Postgres 中.如何创建包含所有外键的现有表的副本.

in Postgres. How can I create a copy of an existing table including all foreign keys.

推荐答案

CREATE TABLE ... LIKE ...中没有自动创建外键的选项.

There is no option to automatically create foreign keys in CREATE TABLE ... LIKE ....

对于文档:

LIKE source_table [like_option ...]

LIKE source_table [ like_option ... ]

非空约束总是被复制到新表中.查看仅当指定了 INCLUDING CONSTRAINTS [...]

Not-null constraints are always copied to the new table. CHECK constraints will be copied only if INCLUDING CONSTRAINTS is specified [...]

原始表上的索引、PRIMARY KEY 和 UNIQUE 约束仅当 INCLUDING INDEXES 子句时才会在新表上创建已指定.

Indexes, PRIMARY KEY, and UNIQUE constraints on the original table will be created on the new table only if the INCLUDING INDEXES clause is specified.

实际上，使用 GUI 工具很容易.例如，在 PgAdmin III 中:

In practice it's easy with GUI tools. For example, in PgAdmin III:

• 将source_table的声明(DDL)复制到查询工具(ctrl-e)，
• 编辑声明，
• 执行 sql.

• copy declaration (DDL) of source_table to query tool (ctrl-e),
• edit the declaration,
• execute sql.

在 SQL 脚本中，您可以使用以下函数.重要假设:源表外键具有正确的名称，即它们的名称包含源表名称(典型情况是什么).

In an SQL script you can use the following function. Important assumption: source table foreign keys have correct names i.e. their names contain source table name (what is a typical situation).

create or replace function create_table_like(source_table text, new_table text)
returns void language plpgsql
as $$
declare
    rec record;
begin
    execute format(
        'create table %s (like %s including all)',
        new_table, source_table);
    for rec in
        select oid, conname
        from pg_constraint
        where contype = 'f' 
        and conrelid = source_table::regclass
    loop
        execute format(
            'alter table %s add constraint %s %s',
            new_table,
            replace(rec.conname, source_table, new_table),
            pg_get_constraintdef(rec.oid));
    end loop;
end $$;

使用示例:

create table base_table (base_id int primary key);
create table source_table (id int primary key, base_id int references base_table);

select create_table_like('source_table', 'new_table');

d new_table

   Table "public.new_table"
 Column  |  Type   | Modifiers 
---------+---------+-----------
 id      | integer | not null
 base_id | integer | 
Indexes:
    "new_table_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
    "new_table_base_id_fkey" FOREIGN KEY (base_id) REFERENCES base_table(base_id)

这篇关于如何在 psql 中使用外键约束将一个表的结构复制到另一个表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！