PosetgreSQL フォーリンキーのDDL出力

フォーリンキー作成DDL出力SQL

pg_get_constraintdef()関数でFK制約が取得できるのですが、完全なDDLではないので足りない部分は自身で補足する必要があります。以下はsooniスキーマのuser_masterテーブルに作成されているFK（フォーリンキー）のDDLを出力する例です。

select 
--n.nspname,t.relname,c.conname,
'alter table '||n.nspname||'.'||t.relname||' add constraint '||c.conname||' '||pg_get_constraintdef(c.oid)||';' as add_constraint_ddl
from pg_constraint c
inner join pg_class t on c.conrelid = t.oid
inner join pg_namespace n on c.connamespace = n.oid
where 1=1
and c.contype = 'f'
and n.nspname='sooni'
and t.relname='user_mst'
order by conname
;

実行例

                                                       add_constraint_ddl
---------------------------------------------------------------------------------------------------------------------------------
 alter table sooni.user_mst add constraint fk_user_mst_address_cd FOREIGN KEY (address_cd) REFERENCES address_mst(address_code);
 alter table sooni.user_mst add constraint fk_user_mst_rep_hh_user_id FOREIGN KEY (rep_hh_user_id) REFERENCES user_mst(user_id);
(2 行)


myposdb=>

フォーリンキー削除DDL出力SQL

select 
--n.nspname,t.relname,c.conname,
'alter table '||n.nspname||'.'||t.relname||' drop constraint '||c.conname||';' as drop_constraint
from pg_constraint c
inner join pg_namespace n
on c.connamespace = n.oid
inner join pg_class t on c.conrelid = t.oid
where 1=1
and c.contype = 'f'
and n.nspname='sooni'
and t.relname='user_mst'
order by conname
;

実行例

                            drop_constraint
------------------------------------------------------------------------
 alter table sooni.user_mst drop constraint fk_user_mst_address_cd;
 alter table sooni.user_mst drop constraint fk_user_mst_rep_hh_user_id;
(2 行)


myposdb=>

FK対象項目一覧

select 
s.table_schema, s.table_name,s.constraint_name,c.ordinal_position, c.column_name
from information_schema.key_column_usage c
inner join information_schema.table_constraints s
on c.table_schema = s.table_schema
and c.table_name = s.table_name
and c.constraint_name = s.constraint_name
where s.constraint_type ='FOREIGN KEY'
--and s.table_schema = 'sooni' and s.table_name ='attended_event'
and s.constraint_name='fk2_attended_event'
order by s.table_schema, s.table_name,s.constraint_name,ordinal_position
;

 table_schema |   table_name   |  constraint_name   | ordinal_position | column_name
--------------+----------------+--------------------+------------------+--------------
 sooni        | attended_event | fk2_attended_event |                1 | event_id
 sooni        | attended_event | fk2_attended_event |                2 | rep_event_id
(2 行)


myposdb=>

FK参照先テーブル対象インデックスと対象カラム一覧

select 
 c.conname -- fk名
,n.nspname -- スキーマ名
,t.relname -- 参照先テーブル
,p.relname -- 参照先テーブルインデックス
,u.ordinal_position
,u.column_name
from pg_constraint c
inner join pg_class p on c.conindid = p.oid  -- この制約をサポートするインデックス
inner join pg_class t on c.confrelid = t.oid  -- この制約をサポートするインデックス
inner join information_schema.key_column_usage u on p.relname = u.constraint_name
inner join pg_namespace n on t.relnamespace = n.oid
where c.conname ='fk2_attended_event'
;

      conname       | nspname |     relname     |      relname       | ordinal_position |  column_name
--------------------+---------+-----------------+--------------------+------------------+---------------
 fk2_attended_event | sooni   | attending_event | pk_attending_event |                1 | event_id
 fk2_attended_event | sooni   | attending_event | pk_attending_event |                2 | event_user_id
(2 行)


myposdb=>