【Oracle12c】一発で主キーの列名(カラム名)を確認するSQL文

今回は一発でテーブルに設定されている主キーの列名(カラム名)を確認できるSQL文を紹介します。

少し複雑なSQL文に見えますが、テーブル名の部分を調べたいテーブル名にするだけでそのまま使えます。

是非参考にしてみてください。

ゆりグル

SQL文の解説もしているよ

主キー検索SQL文
SQL文解説
最後に

主キー検索SQL文

主キー検索SQL：

SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME ='テーブル名' 
AND CONSTRAINT_NAME 
IN 
( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='テーブル名' 
AND CONSTRAINT_TYPE ='P' );

例：EMPというテーブル名の主キーはENPNO

先程のSQLを流して主キーのEMPNOが取得できるかチェックしてみましょう。

Osqleditで実行：

主キーの列名（カラム名）を取得することができました。

SQL文解説

少し複雑なSQL文なので分解して解説していきます。

主キー検索SQL：

SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME ='テーブル名'…①
AND CONSTRAINT_NAME 
IN 
( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='テーブル名' 
AND CONSTRAINT_TYPE ='P'…② );

全体的の構造としてはIN句を用いた副問い合わせをしています。
「USER_CONS_COLUMNS」と「USER_CONSTRAINTS」という2つのテーブルにある「CONSTRAINT_NAME」の項目の値が一致するものを探し、その値を元に列名（カラム名）を検索しています。
1つ1つ見ていきましょう。

①USER_CONS_COLUMNS

「USER_CONS_COLUMNS」はユーザーがすでに持っているもので、制約に指定されている列を示します。

「USER_CONS_COLUMNS」は以下の列を持っています。

列名	詳細
OWNER	所有者名
CONSTRAINT_NAME	制約名
TABLE_NAME	対応するテーブル名
COLUMN_NAME	列名（カラム名）
POSITION	定義内の位置

サンプルを見た方がわかりやすいかもしれません。

実行SQL：

SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME ='テーブル名';

Osqleditで実行：

「CONSTRAINT_NAME」は勝手にシステム側でつけられます。

②USER_CONSTRAINTS

「USER_CONSTRAINTS」も上と同じくユーザーがすでに持っているもので、表の制約定義をすべて示します。

「USER_CONSTRAINTS」は以下の列を持っています。
たくさん持っていますが、重要なのは制約タイプです。今回調べたい主キーの制約タイプは「P」というのを覚えておいてください。

列名	詳細
OWNER	所有者名
CONSTRAINT_NAME	制約名
CONSTRAINT_TYPE	制約タイプ　P:主キー制約　U:一意制約　C:チェック制約　R:参照整合性制約　V:ビューでのチェック・オプション付き　O:ビューで読取り専用　H:ハッシュ式　F:REF列を含む　S:サプリメンタル・ロギング
TABLE_NAME	対応するテーブル名
SEARCH_CONDITION	チェック制約に対する検索条件のテキスト。
SEARCH_CONDITION_VC	チェック制約に対する検索条件のテキスト。
R_OWNER	参照制約で参照される表の所有者
R_CONSTRAINT_NAME	参照表の一意制約の定義名
DELETE_RULE	参照制約の削除ルール CASCADE SET NULL NO ACTION
STATUS	制約の施行状態
DEFERRABLE	制約が遅延可能かそうではないか
DEFERRED	制約が初期状態から遅延されていたか
VALIDATED	データがすべて制約に従っているかどうか（VALIDATED またはNOT VALIDATED ）
GENERATED	制約の名前がユーザーにより生成されたかシステムによって生成されたか
BAD	この制約があいまいな方法で世紀を指定するか
RELY	この列は、クエリー・リライトのときに制約が考慮されるか
LAST_CHANGE	制約が最後に変更された日時
INDEX_OWNER	INDEXの所有者
INDEX_NAME	INDEXの名称
INVALID	制約の有効無効有効:(NULL)　無効:INVALID
VIEW_RELATED	制約がビューによって異なるか
ORIGIN_CON_ID	データの発生元のコンテナのID。

実行SQL：

SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME ='テーブル名';

Osqleditで実行：

副問い合わせ

副問い合わせとは、別のテーブルのSELECT文の結果をSQL文で利用することです。

今回の場合だと、上記で説明した2つのテーブル「USER_CONS_COLUMNS」と「USER_CONSTRAINTS」を使い、共通の項目である「CONSTRAINT_NAME」の値を元に
探したい値を出します。

今回求めたい値は主キーの列名（カラム名）です。
「USER_CONSTRAINTS」で主キーを調べられます。
しかし、「USER_CONSTRAINTS」では、主キーがわかってもカラム名まで調べられません。
ですので、「USER_CONS_COLUMNS」を使って主キーのカラム名を調べます。
この2つに共通している項目が「CONSTRAINT_NAME」です。

上記の文章を図で見てみましょう。

求め方としては、
「USER_CONSTRAINTS」で主キーに絞って出します。

次に「USER_CONS_COLUMNS」で上で求めた「CONSTRAINT_NAME」を元に列名（カラム名）を求めます。

2回SQL文を流す手間を省き、一つにまとめてくれるのが、副問い合わせです。IN句を使っています。

主キー検索SQL：

SELECT COLUMN_NAME FROM USER_CONS_COLUMNS WHERE TABLE_NAME ='テーブル名'
AND CONSTRAINT_NAME 
IN 
( SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME ='テーブル名' 
AND CONSTRAINT_TYPE ='P');