Oracle SQL 表の作成
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
表の作成構文
CREATE TABLE [<スキーマ名>.]<表名> (
<列名1> <データ型> [ [NOT NULL] | [UNIQUE] | [PRIMARY KEY] ... ] ,
<列名2> <データ型> [ [NOT NULL] | [UNIQUE] | [PRIMARY KEY] ... ] ,
<列名3> <データ型> [ [NOT NULL] | [UNIQUE] | [PRIMARY KEY] ... ] ,
...
);
スキーマ名はいわゆるユーザ名のことで省略すれば現在ログインしているスキーマに対して表は作成されます。
但し、前提条件としてログインスキーマに対して表の作成権限が与えられていないとこのCREATE文は失敗します。
データ型は文字列型，数値型，日付型等がありますが、以下に使用頻度の高い型についてまとめてあります。
このCREATE文は非常に簡単な構文を示していますが、実際はさらに複雑な構文で表に対していろいろな性格
付けを行えます。例えば、表のデータベースのどの領域に作成されるのかとか、表の領域はどのくらいの大きさ
なのかといったことです。この簡単な構文でもOracleはデフォルト値を使用して表を作成しますので、最初のうちは
あまり心配しなくても問題はありません。
データ型
特別なことをしなければ、ほとんどの表は以下の４個の型で列を定義できると思います。
| データ型 | 宣言 | 備 考 |
|---|---|---|
| 固定長文字列型 | CHAR(n) | n：で指定された領域を表に固定長で確保する。 nより短い文字列を設定すると残りには空白が設定され、 nより長い文字列を設定するとOracleはエラーを返す。 許容最大：2000バイト コード及び日付の様な文字列が固定的な長さの場合に 用いると良いでしょう。 |
| 可変長文字列型 | VARCHAR2(n) | nは文字列を保持できる最大の領域長を設定する。 nを超えない限りは設定された文字列そのものを保持します。 許容最大：4000バイト 名前、住所等の割と文字列が長くて可変な場合に用いる。 |
| 数値型 | NUMBER(p,s) | p：小数点以下も含んだ数値全体の桁数(最大38桁) s：小数点以下の桁数 NUMBER(p)の指定も可能でこの場合はNUMBER(p,0)と同様 NUMBERのみの場合は浮動小数点数を示します。 |
| 日付型 | DATE | 日付型と呼ばれますが、日付と時刻のデータを持っています。 DATE型の整数部に日付データを、小数部に時刻データを持つ。 |
列属性
<NOT NULL>属性
NULLは列データに値が何も設定されていない状態を意味します。NOT NULL属性に指定された
列にはNULLを設定できません。INSERT命令、UPDATE命令でその列にNULLを設定するとエラーが
発生してその命令は失敗します。
<UNIQUE>属性
この属性は設定された列に同じ値が2個以上存在することができません。つまりユニークな値
（一意）で無ければなりません。
<PRIMARY KEY>属性
この属性は設定された列に主キーを設定するものです。主キーとは表の行を一意に識別する
ための列を使って主索引を設定することです。
<PRIMARY KEY>属性を設定すると当然<UNIQUE>属性,<NOT NULL>属性を指定したことになります。
尚、<PRIMARY KEY>属性はCREATE文のなかで1回しか指定できません。
以下のSQL文は<PRIMARY KEY>属性に設定されているTM_担当者の担当コードに重複してINSERTを
実行したときのエラー及び、NULL値を設定しようとした時のエラーの発生の例を示します。
(SQL実行の中で"DEMO."は私がテストで使用していたスキーマ名です)
SQL> INSERT INTO TM_担当者 VALUES(10,1,'吉田',TO_DATE('1960/01/10'),1);
1行が作成されました。
SQL> INSERT INTO TM_担当者 VALUES(10,2,'山本',TO_DATE('1962/05/09'),1);
INSERT INTO TM_担当者 VALUES(10,2,'山本',TO_DATE('1962/05/09'),1)
*
1行でエラーが発生しました。
ORA-00001: 一意制約(DEMO.SYS_C006074)に反しています
SQL> INSERT INTO TM_担当者 VALUES(NULL,2,'山本',TO_DATE('1962/05/09'),1);
INSERT INTO TM_担当者 VALUES(NULL,2,'山本',TO_DATE('1962/05/09'),1)
*
1行でエラーが発生しました。
ORA-01400: ("DEMO"."TM_担当者"."担当者コード")にはNULLは挿入できません。
表の複数の列に主キーを設定する場合について、例を示しておきます。
SQL> CREATE TABLE TT_T1(
2 CD1 NUMBER(3) PRIMARY KEY
3 ,CD2 NUMBER(3) PRIMARY KEY
4 );
,CD2 NUMBER(3) PRIMARY KEY
*
3行でエラーが発生しました。
ORA-02260: 表には主キーを1つのみ持つことができます
SQL> CREATE TABLE TT_T1(
2 CD1 NUMBER(3)
3 ,CD2 NUMBER(3)
4 ,PRIMARY KEY(CD1 , CD2)
5 );
表が作成されました。
尚、以下の様に表の作成と主キーの作成を別のSQL文にて行うこともできます。
SQL> CREATE TABLE TT_T1( 2 CD1 NUMBER(3) 3 ,CD2 NUMBER(3) 4 ); 表が作成されました。 SQL> ALTER TABLE TT_T1 2 ADD CONSTRAINT PKEY_TANTO 3 PRIMARY KEY (CD1 , CD2); 表が変更されました。
表の列の追加、及び変更
ALTER TABLE <表名>
[ ADD | MODIFY ] <列名1> <データ型>
[ ADD | MODIFY ] <列名2> <データ型>
[ ADD | MODIFY ] <列名3> <データ型>
...
;
表の列はデータベースを運用していくうちにアプリケーションの変更などにより、追加や変更が必要になってきます。
この作業を行う為の命令がALTER文です。ADDは列の追加を、MODIFYは列の変更を指示します。
Oracleの場合は他のデータベースの様にDROP命令が無く、一旦作成した列は削除できません。
尚､列を変更する場合は同じデータ型で、データ長を変更するぐらいに留めておいた方が良いと思います。
以下に、列の追加する例を示します。
SQL> ALTER TABLE TT_売上明細 2 ADD 備考 VARCHAR2(40); 表が変更されました。 SQL> desc TT_売上明細 名前 NULL? 型 ----------------------------------------------------- -------- ------------------------------------ 売上番号 NOT NULL NUMBER(8) 明細番号 NOT NULL NUMBER(3) 商品コード NUMBER(6) 売上数量 NUMBER(6) 備考 VARCHAR2(40)
以下に、列を変更する例を示します。
SQL> ALTER TABLE TT_売上明細 2 MODIFY 備考 VARCHAR2(20); 表が変更されました。 SQL> desc TT_売上明細 名前 NULL? 型 ----------------------------------------------------- -------- ------------------------------------ 売上番号 NOT NULL NUMBER(8) 明細番号 NOT NULL NUMBER(3) 商品コード NUMBER(6) 売上数量 NUMBER(6) 備考 VARCHAR2(20)
以下に、MODIFYが失敗する例を示します。
SQL> UPDATE TT_売上明細
2 SET 備考 = '1234567890aaaaaaaaaabbbbbbbbbbcccccccccc'
3 WHERE 売上番号 = 1
4 AND 明細番号 = 1;
1行が更新されました。
SQL> commit;
コミットが完了しました。
SQL> select * from TT_売上明細
2 ;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2 1234567890aaaaaaaaaabbbbbbbbbbcccccccccc
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 1
13行が選択されました。
SQL> ALTER TABLE TT_売上明細
2 MODIFY 備考 VARCHAR2(20);
MODIFY 備考 VARCHAR2(20)
*
エラー行: 2: エラーが発生しました。
ORA-01441: 長さを短くする列は空でなければなりません。
TT_売上明細の備考をVARCHAR2(40)からVARCHAR2(20)に変更しようとしましたが、既に20バイトを超えるデータが存在した為、20バイトへの変更は失敗しました。このことは何も文字列型だけに起こるわけではなく、数値型の桁数変更の場合にも起こる可能性が在ります。
表の結合で使用した表の作成SQL文
以下に各表の作成のCREATE文を示します。
CREATE TABLE TM_商品(
商品コード NUMBER ( 6) PRIMARY KEY
,商品名 VARCHAR2(40)
,商品区分 VARCHAR2(20)
,仕入単価 NUMBER ( 9)
,売上単価 NUMBER ( 9)
);
CREATE TABLE TM_担当者(
担当者コード NUMBER ( 3) PRIMARY KEY
,上司コード NUMBER ( 3) /* 上司の担当者コード(自分が主任はNULL) */
,担当者名 VARCHAR2(20)
,生年月日 DATE
,性別 NUMBER ( 1) /* 1:男性 , 2:女性 */
);
CREATE TABLE TM_得意先(
得意先コード NUMBER ( 6) PRIMARY KEY
,得意先名 VARCHAR2(20)
,住所 VARCHAR2(40)
,電話番号 VARCHAR2(16)
,請求締日 VARCHAR2( 2) /*末日:99*/
);
CREATE TABLE TT_売上(
売上番号 NUMBER ( 8) PRIMARY KEY
,売上日 DATE
,得意先コード NUMBER ( 6)
,担当者コード NUMBER ( 3)
);
CREATE TABLE TT_売上明細(
売上番号 NUMBER ( 8)
,明細番号 NUMBER ( 3)
,商品コード NUMBER ( 6)
,売上数量 NUMBER ( 6)
);
ALTER TABLE TT_売上明細 ADD PRIMARY KEY (売上番号,明細番号);
