CREATE TABLE
テーブルの定義は、CREATE TABLE という DDL (Data Definition Language) にて行なう。
DDL とは スキーマ を作成、変更、削除するときに使用する SQL の総称
テーブル定義
- 利用者マスタの作成
以下のようなレイアウトの表(USER_MASTER)を作成したい場合
| 日本語名 | カラム名 | 入力必須 | データの型と大きさ | 省略時 |
| 利用者ＩＤ | USER_ID | YES | 8 バイト以内の文字列 | |
| 部門 | DEPT_NO | NO | 8 バイト以内の文字列 | |
| 利用者名 | USER_NAME | NO | 32 バイト以内の文字列 | |
| 作成日 | CREATED_ON | NO | 日付(日＋時間) | SYSDATE |
| 更新日 | MODIFIED_ON | NO | 日付(日＋時間) | |
SQLPlus にて、以下の DDL 文を実行する。
CREATE TABLE USER_MASTER
(
USER_ID VARCHAR2(8) NOT NULL,
DEPT_NO VARCHAR2(8),
USER_NAME VARCHAR2(32),
CREATED_ON DATE DEFAULT SYSDATE,
MODIFIED_ON DATE
);
- VARCHAR2(桁数)、DATE
- あらかじめ組み込まれている データ型 の一種で VARCHAR2 は可変長文字列 ( ) 内はその精度を指定する。DATE は日付 + 時刻の型。
- NOT NULL
- NULL (値のない状態を示す単語)を許可しないことを意味する。
- DEFAULT デフォルト値
- 行を作成したときに値が指定されてない場合に、その初期値を設定する(SYSDATE は関数の一つで現在日付をあらわす)。
カラムのデフォルト値は一度設定すると削除できない。削除するかわりにデフォルト値を NULL にするかテーブルを作り直す。（個人的には気持ち悪いから作り直します）
この文で作成したテーブルは現在ログオンしているスキーマ(空間)上に作成され、自分がテーブルの所有者になる。このスキーマの概念があるため、ログオンユーザーが異なれば、同じテーブル名をつけることが可能。
テーブル名に使用してはいけない名前（公式）
- SYS_で始まる名前
- ORA_ で始まる名前
- ユーザー定義のファンクションの名前
- SQL 組込みファンクションの名前 (例： FLOOR、LOG、PATH など）
構築時にはエラーにはならないので気づかないがファンクションと同名のテーブル名を作ると開発フェーズでトラブルになる。
☆ガイドライン的な意味で テーブルの名前に日本語を使用するのも避けることをおすすめする。
将来 ファンクションや機能になりそうな汎用的で短い名称（例：BROWSE、GUIDE、ROUTE）や 各種予約語 、他の DBMS の予約語なども参考にして事前に避けておく方がよい。
今のままだと、同じユーザーのデータを 2 回登録できてしまい無駄な上メンテナンス性において都合が悪くなる。
課題
同じユーザーを 2 回登録させるのを防止したい
⇒ USER_IDで二重登録できないように自動的に制御してもらいたい。
実現方法
利用者ID(USER_ID)に対してプライマリキー(PRIMARY KEY)を作成する。
その名前は USER_MASTER_PK にする。
名無しでも作成できるのですが、できるだけ名前は自分で付けるようにしてください。
- ☆ガイドライン: プライマリキー
- プライマリキーの名前は、テーブル名+'_PK' などで統一すると良い。
SQL*Plus にて、以下の DDL 文を実行する。
プライマリキーを作成するDDL文
ALTER TABLE USER_MASTER ADD CONSTRAINT USER_MASTER_PK PRIMARY KEY (USER_ID) ;
NOTE:PRIMARY KEYは (COL1, COL2, ・・・,COL32) 形式で 最大32個によるの 複合主キー を作成可能 （Oracle 10g 時点）
プライマリキーを削除する
プライマリキーを削除するDDL文（制約を汎用的に削除する）
ALTER TABLE USER_MASTER DROP CONSTRAINT USER_MASTER_PK ;
プライマリキーの制約名を指定していない場合には削除するための名前がわからないので
ALTER TABLE USER_MASTER DROP PRIMARY KEY ;
というプライマリキー専用の構文も用意されている。
テーブルの定義を一緒にプライマリキーの定義をする方法もあるが、制限もあるので統一して別々に定義することをお勧めする。
テーブル定義の複製 CTAS (Create Table As Select)
CREATE TABLE AS SELECT を使用するとテーブルの定義とデータの複製を同時に行なうことができる。
但し、プライマリキーやインデックスなどは複製されない。
CREATE TABLE COPY_OF_A AS SELECT * FROM A
データを複製したくない場合には WHERE で１件もヒットしないようにすれば良い。
CREATE TABLE COPY_OF_A AS SELECT * FROM A WHERE 1=0; -- カラム情報だけの複製
関連事項
