PostgreSQL 制約一覧

table_constraints

information_schemaのtable_constraintsビューを参照する事で、制約を確認できます。フォーリンキー一覧やプライマリーキー一覧を取得できます。

制約一覧出力SQL

SELECT
c.constraint_schema
,c.constraint_type
,c.constraint_name,c.table_schema,c.table_name
FROM information_schema.table_constraints c
WHERE 1=1
and constraint_schema='sooni'
order by c.constraint_type
;

制約一覧出力例

myposdb=# SELECT
myposdb-# c.constraint_schema
myposdb-# ,c.constraint_type
myposdb-# ,c.constraint_name,c.table_schema,c.table_name
myposdb-# FROM information_schema.table_constraints c
myposdb-# WHERE 1=1
myposdb-# and constraint_schema='sooni'
myposdb-# order by c.constraint_type
myposdb-# ;
 constraint_schema | constraint_type |    constraint_name     | table_schema | table_name
-------------------+-----------------+------------------------+--------------+------------
 sooni             | CHECK           | 16393_17625_2_not_null | sooni        | hoge
 sooni             | CHECK           | 16393_17250_1_not_null | sooni        | logging
 sooni             | CHECK           | 16393_17514_1_not_null | sooni        | addsample
 sooni             | CHECK           | 16393_17613_1_not_null | sooni        | wardcity
 sooni             | CHECK           | 16393_17625_1_not_null | sooni        | hoge
 sooni             | CHECK           | 16393_16467_1_not_null | sooni        | cable
 sooni             | CHECK           | 16393_16467_2_not_null | sooni        | cable
 sooni             | CHECK           | 16393_16483_1_not_null | sooni        | spec
 sooni             | FOREIGN KEY     | hoge_prev_id_fkey      | sooni        | hoge
 sooni             | PRIMARY KEY     | spec_pkey              | sooni        | spec
 sooni             | PRIMARY KEY     | hoge_pkey              | sooni        | hoge
 sooni             | PRIMARY KEY     | cable_pkey             | sooni        | cable
(12 行)


myposdb=#

PK一覧（構成項目含む）出力SQL

select c.constraint_type,u.table_schema,u.table_name,u.constraint_name,u.ordinal_position,u.column_name 
from  information_schema.table_constraints c inner join information_schema.key_column_usage u
on c.table_schema = u.table_schema and c.table_name = u.table_name and c.constraint_name = u.constraint_name
where c.table_schema='apuser'
and c.constraint_type='PRIMARY KEY'
order by c.constraint_type,u.constraint_schema,u.constraint_name,u.table_schema,u.table_name,u.ordinal_position,u.column_name
;

フォーリンキー一覧（構成項目含む）出力SQL

select c.constraint_type,u.table_schema,u.table_name,u.constraint_name,u.ordinal_position,u.column_name 
from  information_schema.table_constraints c inner join information_schema.key_column_usage u
on c.table_schema = u.table_schema and c.table_name = u.table_name and c.constraint_name = u.constraint_name
where c.table_schema='apuser'
and c.constraint_type='FOREIGN KEY'
order by c.constraint_type,u.constraint_schema,u.constraint_name,u.table_schema,u.table_name,u.ordinal_position,u.column_name
;

DDL出力するにはこちら

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

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

oha-yo.com

2023.03.28