Oracle SQL 表の作成


表の作成構文

CREATE TABLE [<スキーマ名>.]<表名> (
        <列名1> <データ型> [ [NOT NULL] | [UNIQUE] | [PRIMARY KEY] ... ] ,
        <列名2> <データ型> [ [NOT NULL] | [UNIQUE] | [PRIMARY KEY] ... ] ,
        <列名3> <データ型> [ [NOT NULL] | [UNIQUE] | [PRIMARY KEY] ... ] ,
        ...
);

スキーマ名はいわゆるユーザ名のことで省略すれば現在ログインしているスキーマに対して表は作成されます。
但し、前提条件としてログインスキーマに対して表の作成権限が与えられていないとこのCREATE文は失敗します。
データ型は文字列型,数値型,日付型等がありますが、以下に使用頻度の高い型についてまとめてあります。

 このCREATE文は非常に簡単な構文を示していますが、実際はさらに複雑な構文で表に対していろいろな性格
付けを行えます。例えば、表のデータベースのどの領域に作成されるのかとか、表の領域はどのくらいの大きさ
なのかといったことです。この簡単な構文でもOracleはデフォルト値を使用して表を作成しますので、最初のうちは
あまり心配しなくても問題はありません。

データ型

特別なことをしなければ、ほとんどの表は以下の4個の型で列を定義できると思います。

データ型 宣言 備    考
固定長文字列型 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 (売上番号,明細番号);




ページのトップへ戻る