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

Oracle12c

今回は一発でテーブルに設定されている主キーの列名(カラム名)を確認できる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_OWNERINDEXの所有者
INDEX_NAMEINDEXの名称
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');

最後に

少し複雑でなかなかわかりにくかったのではないでしょうか。
何も考えずコピペでもOKですが、時間があったら是非意味まで理解できるといいですね。

コメント

タイトルとURLをコピーしました