今回は一発でテーブルに設定されている主キーの列名(カラム名)を確認できる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 | 参照制約の削除ルール
|
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');
最後に
少し複雑でなかなかわかりにくかったのではないでしょうか。
何も考えずコピペでもOKですが、時間があったら是非意味まで理解できるといいですね。
コメント