时间戳

首页 > 解决方案 > 查找所有使用需要更改类型的列的主键和外键

sql - 查找所有使用需要更改类型的列的主键和外键

问题描述

我需要将列数据类型从 int4 更改为 varchar(30)，并且此列是其他几个表中的主键和外键。因此，我试图列出所有使用此列的 pks 和 fks，并在更改所有这些表和约束之后。

所以我有两个问题：

1）有更聪明的方法来做这种类型的改变？

2) 我怎样才能列出所有这些 pks 和 fks 每一行？（使用下面的选择，约束中的每一列都有一行）

使用 postgres 10.10

select 
    tco.constraint_name,
    tco.constraint_type,
    kcu.table_schema,
    kcu.table_name,
    ccu.table_schema,
    ccu.table_name,
    ccu.column_name
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tco.constraint_name
where 
    ( tco.constraint_type = 'PRIMARY KEY' or  tco.constraint_type = 'FOREIGN KEY' )
    and 
    kcu.column_name='column_name'

标签： sqlpostgresql

解决方案

无论您想要实现的目标是什么，这里是（我认为）在您的“2）”上做您想要的代码。

测试结构：

drop table if exists test;
create table test (id int primary key);
create table test_2 (id int, id_1 int);
alter table test_2 add constraint pk_test_2 primary key (id, id_1);
alter table test_2 add constraint fk_test_2_test_1 foreign key (id_1) references test (id);
create table test_3 (id int primary key, id_2 int, id_1 int);
alter table test_3 add constraint fk_test_3_test_2 foreign key (id_2, id_1) references test_2 (id, id_1);

查询：

SELECT
    rel.relname as table_name
    , ccu.relname as ref_table_name
    , pg_get_constraintdef(con.oid) as con_def                      
    , con.conname
    , col.attname
FROM pg_catalog.pg_constraint con                                   
    INNER JOIN pg_catalog.pg_class rel                              
        ON rel.oid = con.conrelid
    INNER JOIN pg_catalog.pg_namespace nsp                          
        ON nsp.oid = connamespace                           
    left outer JOIN pg_class AS ccu 
        ON con.confrelid = ccu.oid
    JOIN LATERAL UNNEST(con.conkey) WITH ORDINALITY AS u(attnum, attposition) ON TRUE
    JOIN pg_attribute col ON (col.attrelid = rel.oid AND col.attnum = u.attnum)
WHERE nsp.nspname = 'public'
    and col.attname = 'id';

或类似的东西：

select 
    tco.constraint_name,
    tco.constraint_type,
    kcu.table_schema,
    kcu.table_name,
    ccu.table_schema,
    ccu.table_name,
    string_agg(distinct ccu.column_name, '; ')
from information_schema.table_constraints tco
join information_schema.key_column_usage kcu 
     on kcu.constraint_name = tco.constraint_name
     and kcu.constraint_schema = tco.constraint_schema
     and kcu.constraint_name = tco.constraint_name
JOIN information_schema.constraint_column_usage ccu ON ccu.constraint_name = tco.constraint_name
where 
    ( tco.constraint_type = 'PRIMARY KEY' or  tco.constraint_type = 'FOREIGN KEY' )
    and tco.constraint_schema = 'public'
group by tco.constraint_name,
    tco.constraint_type,
    kcu.table_schema,
    kcu.table_name,
    ccu.table_schema,
    ccu.table_name;

推荐阅读