Oracle SQL 表の作成
おすすめ書籍
本書用のWebアプリを使ってSQLを実行し学習していきます。初心者の方に分かりやすく、図表を多く使って解説されています。 学習の最後には200問越えのドリルが付いていて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 (売上番号,明細番号); |
