はじめに
テーブル定義書及び、既存テーブル調査にあたり以下のことで戸惑いました。
- テーブル定義書にどこまで何を盛り込むのか
- 既存テーブルの調査にあたり、データベース管理システムによりかなり異なる。
既存テーブル調査はツールを使えば簡易ですが、コマンド、SQL等で調査する場合、データベース管理システムの違いでかなり違うことを感じました。
SQLを書いていたときはどんなデータベース管理システムを使おうが構文違いを把握し対応できると考えていましたが、それ以上のことを行うとそうでもなかった。
OracleをSQLでしか対応したことはない人はかなり戸惑うのではないでしょうか。
備忘記録として上記調べたことを残します。
1.テーブル定義書を書くにあたって
テーブル定義書を書くに当たって、全体を理解するにあたり考慮しておいた方がいいことを記載します。
いきなり書くと、漏れ漏れの定義所になります。
テーブル定義について、例えば以下を例にします。
テーブル名、データ型、長さ、主キーとかはすぐに浮かびますが、抜け気味になるのは制約、インデックスあたりかと思います。
| テーブル名 | 列名 | データ型 | 長さ | 制約 | 説明 |
|---|---|---|---|---|---|
| Employees | ID | INT | 10 | Primary Key, Not Null | 従業員の一意のID |
| Employees | FirstName | VARCHAR | 255 | Not Null | 従業員の名前 |
| Employees | LastName | VARCHAR | 255 | Not Null | 従業員の姓 |
| Employees | Age | INT | 3 | Check (Age >= 18) | 従業員の年齢 |
| Orders | OrderID | INT | 10 | Primary Key, Not Null | 注文の一意のID |
| Orders | OrderNumber | INT | 10 | Unique, Not Null | 注文の一意の番号 |
| Orders | EmployeeID | INT | 10 | Foreign Key (References Employees(ID)) | 注文を処理した従業員のID |
1-1. そもそもテーブル定義とは何か？
テーブル定義は、データベースのテーブルがどのように構成されているかを定義したものです。
テーブル定義には、テーブルに含まれる列の名前、データ型、そして制約（必要に応じて）が含まれます。
1.2. 制約とは何か？
制約は、データベーステーブルの列に対して設けられるルールで、不適切なデータの挿入や更新を防ぐために使用されます。以下の種類があります：
しっかり以下の存在があることを念頭においてください。
- 主キー制約（Primary Key）：列の値が一意であること、およびNULLでないこと。
- 外部キー制約（Foreign Key）：列の値が他のテーブルの主キー列の値と一致すること。
- 一意制約（Unique）：列の値が一意であること。
- NOT NULL制約：列の値がNULLでないこと。
- チェック制約（Check）：列の値が特定の条件を満たす。
1.3. インデックスとは何か？
インデックスは、特定のテーブルのデータを高速に検索・取得するためのデータ構造です。主キー制約を持つ列には自動的にインデックスが作成されますが、他の列に対してもインデックスを手動で作成することができます。
1.3.1 複合インデックス
複合インデックスは、2つ以上の列を組み合わせて作成されるインデックスです。特定のクエリのパフォーマンスを最適化するために使用されます。
2.既存テーブル調査
どのテーターベース管理システムを使用しても上記を考慮することになりますが、データベース管理システムによってはテーブル定義の確認の仕方が異なります。
create文から確認するのが早いかと思いますが、必ずしもそれがあるとは限りません。
データベースによってその確認方法が結構違ったりするので、OracleとMySQLを例に取って記載します。
2.1 OracleとMySQLでの制約とインデックスの確認方法
OracleとMySQLでは、制約とインデックスの確認方法が異なります。
Mysqlの感覚でOracleを調査しようとすると衝撃を受けます。
2.2 MySQLの制約、インデックスの確認
MySQLではSHOW INDEX FROM テーブル名やSHOW CREATE TABLE テーブル名といったコマンドを使用します。
SHOW INDEXES FROM your_table_name;
DESCRIBE your_table_name;
大体こんな感じで調査できます。
ではOracleはどう調べるかわかりますでしょうか。
2.3 Oracleの制約、インデックスの確認
OracleではUSER_CONSTRAINTS、USER_CONS_COLUMNS、USER_INDEXESなどのデータベース・ディクショナリ・ビューを利用します。
SELECT
constraint_name,
constraint_type,
search_condition,
r_constraint_name
FROM
user_constraints
WHERE
table_name = 'YOUR_TABLE_NAME';
・'YOUR_TABLE_NAME'は、制約を確認したいテーブルの名前です（大文字で！、Oracleは大文字です！）。
・constraint_nameは制約の名前を示します。
・constraint_typeは制約の種類を示します。以下のような値があります：
'P'：主キー制約
'R'：外部キー制約
'U'：一意制約
'C'：チェック制約とNOT NULL制約
search_conditionは、チェック制約の場合にその条件を示します。
・r_constraint_nameは、外部キー制約の場合に参照先の主キー制約名を示します。
Oracleのインデックスを確認するには以下のようになります。
SELECT i.index_name, i.index_type, ic.column_name, ic.column_position
FROM all_indexes i
JOIN all_ind_columns ic ON i.index_name = ic.index_name
WHERE i.table_name = 'YOUR_TABLE_NAME' AND i.owner = 'YOUR_SCHEMA_NAME'
ORDER BY i.index_name, ic.column_position;
このクエリでは、YOUR_TABLE_NAMEを実際のテーブル名に、YOUR_SCHEMA_NAMEをスキーマ名に置き換えます。このクエリによって、インデックス名、インデックスタイプ、インデックスに含まれる列名、および列の位置が表示されます。インデックス名とインデックスタイプを特定の順序で表示したい場合は、ORDER BY句を適宜変更します。
終わりに
テーブル定義書を書くに当たってさらっと抑えるべき箇所と、既存テーブルの調査にあたりデータベース管理システム毎にいかに異なるかを記載しました。
特にOracle箇所が何書いているのさっぱりわからない方はSQLまでしか対応していないと思います。
Oracle初心者は上記くらいは心得ておいた方がいいと思います。
あと、本件把握にあたりchatgptを使用しており、出典が不明です。おいおい出典先は記載していこうと思います。