【DB入門】第3章 テーブルを作る

第1章で確認した部署テーブルと社員テーブルを、CREATE TABLE で作ります。列ごとにデータ型を決め、主キー・外部キー・NOT NULL・CHECK といった制約を付けて、正しくないデータが入らないようにします。あわせて、ID の番号を自動で振るためのシーケンスも作ります。

この章で学ぶこと

• CREATE TABLE の書き方
• データ型（NUMBER・VARCHAR2・DATE）
• 制約（NOT NULL・PRIMARY KEY・REFERENCES・CHECK）と、列制約・表制約の書き分け
• 番号を払い出すシーケンスと CREATE SEQUENCE
• テーブルを消す DROP TABLE と、作り直すときの順番

3.1 この章で作るもの

この章では、dbapp スキーマの中に次の4つを作ります。

種類	名前	役割
シーケンス	SEQ_DEPT	部署ID の番号を 1 から順に払い出す
シーケンス	SEQ_EMP	社員ID の番号を 1 から順に払い出す
テーブル	DEPARTMENT	部署テーブル（部署ID・部署名）
テーブル	EMPLOYEE	社員テーブル（社員ID・氏名・性別・年齢・生年月日・部署ID・住所）

社員テーブルは部署テーブルを参照する（外部キーを持つ）ので、部署テーブルを先に 作ります。

3.2 シーケンスとテーブルを作る

SQL Developer を起動し、「接続」パネルで XE_DBAPP をダブルクリックして接続します（ワークシートが開かない場合は、XE_DBAPP を右クリック → 「SQLワークシートを開く」）。ワークシートのタブに XE_DBAPP と表示されていることを確認してから、次の SQL を入力し、F5（スクリプトの実行）を押します。

ワークシート（XE_DBAPP）

-- 部署IDの番号を振るシーケンス
CREATE SEQUENCE SEQ_DEPT NOCACHE;

-- 社員IDの番号を振るシーケンス
CREATE SEQUENCE SEQ_EMP NOCACHE;

-- 部署テーブル
CREATE TABLE DEPARTMENT (
  DEPT_ID    NUMBER(4)  CONSTRAINT PK_DEPARTMENT PRIMARY KEY,
  DEPT_NAME  VARCHAR2(20 CHAR)  NOT NULL
);

-- 社員テーブル
CREATE TABLE EMPLOYEE (
  EMP_ID      NUMBER(6)  CONSTRAINT PK_EMPLOYEE PRIMARY KEY,
  NAME        VARCHAR2(20 CHAR)  NOT NULL,
  GENDER      NUMBER(1)  NOT NULL
    CONSTRAINT CK_EMPLOYEE_GENDER CHECK (GENDER IN (1, 2)),
  AGE         NUMBER(3)  NOT NULL,
  BIRTH_DATE  DATE       NOT NULL,
  DEPT_ID     NUMBER(4)  NOT NULL
    CONSTRAINT FK_EMPLOYEE_DEPARTMENT REFERENCES DEPARTMENT (DEPT_ID),
  ADDRESS     VARCHAR2(10 CHAR)  NOT NULL
);

「スクリプト出力」に、次の4行が表示されれば成功です。

スクリプト出力

Sequence SEQ_DEPTは作成されました。

Sequence SEQ_EMPは作成されました。

Table DEPARTMENTは作成されました。

Table EMPLOYEEは作成されました。

図3-1 シーケンスとテーブルを作成する SQL を実行したあと

【ご注意】エラーが出たら、原因を直してから、できた分を消してやり直す

途中の SQL でエラーが出ると、その SQL だけが失敗し、前後の SQL は実行されます。たとえば CREATE TABLE EMPLOYEE だけが失敗した場合は、SQL を直してその部分だけを実行し直せば済みます。どこまでできたか分からなくなったときは、3.7 の手順ですべて消してから、もう一度最初から実行してください。

CREATE TABLE の形

この章で使う CREATE TABLE・CREATE SEQUENCE・DROP TABLE は、テーブルなどの入れ物の形を決める DDL です（第1章）。上で実行した SQL は、そのまま部署テーブルと社員テーブルの DDL になります。現場で「このテーブルの DDL をください」と言われたら、このような CREATE TABLE 文を渡します。

CREATE TABLE は、次の形で書きます。

書き方

CREATE TABLE テーブル名 (
  列名  データ型  制約,
  列名  データ型  制約,
  ...
);

• ( と ) の間に、列の定義を ,（カンマ）で区切って 並べます。最後の列の後ろには , を付けません
• 列の定義は「列名、データ型、制約」の順に書きます。制約は無くてもかまいません
• 列と列の間の空白や改行は自由です。この教材では、列名・データ型・制約の位置をそろえて読みやすくしています

3.3 作成したテーブルを確認する

作成したテーブルは、「接続」パネルで確認できます。

1. 「接続」パネルで XE_DBAPP の左の「＋」（または「▶」）をクリックして展開します。

2. 「表」 を展開すると、DEPARTMENT と EMPLOYEE が表示されます。表示されない場合は、「表」を右クリックして リフレッシュ を選んでください。

   
   図3-2 「表」の下に DEPARTMENT と EMPLOYEE が表示される

3. EMPLOYEE をダブルクリックすると、右側に列の一覧が表示されます。列名やデータ型が SQL のとおりになっているか確認しましょう。「Null可能」の列が No になっているのは、NOT NULL を付けた列です。

   
   図3-3 EMPLOYEE の列の一覧

4. 上の「制約」タブをクリックすると、このテーブルに付いている制約の一覧が表示されます。PK_EMPLOYEE・FK_EMPLOYEE_DEPARTMENT・CK_EMPLOYEE_GENDER と、SYS_C で始まる名前の制約（NOT NULL に Oracle が自動で付けた名前）が並びます。

   
   図3-4 EMPLOYEE の「制約」タブ

5. 同じように 「順序」 を展開すると、作成したシーケンス SEQ_DEPT と SEQ_EMP が表示されます。SQL Developer では、シーケンスは「順序」という名前で表示されます。

   
   図3-5 「順序」の下に SEQ_DEPT と SEQ_EMP が表示される

3.4 解説：データ型

列には、どんな種類の値を入れるかを表す データ型 を必ず指定します。この教材で使うのは次の3つです。

データ型	意味	この教材では
NUMBER(n)	最大 n 桁の整数。NUMBER(3) なら -999〜999	ID、性別、年齢
VARCHAR2(n CHAR)	最大 n 文字 の文字列。長さは値に合わせて変わる	氏名、部署名、住所
DATE	日付。Oracle の DATE は時刻（時・分・秒）も持つが、この教材では日付だけを使う	生年月日

【知識メモ】VARCHAR2 に CHAR を付ける理由

VARCHAR2(20) のように CHAR を付けないと、長さは バイト数 で数えられます。日本語は1文字が3バイトになることが多いので、VARCHAR2(20) には漢字が6文字ほどしか入りません。VARCHAR2(20 CHAR) と書けば、日本語でも英数字でも20文字まで入ります。

【知識メモ】小数を入れる NUMBER

NUMBER(5, 2) のように2つの数を書くと、全体で5桁、そのうち小数点以下が2桁（例：123.45）の数を入れられます。この教材では整数しか使わないので、NUMBER(n) だけを使います。

3.5 解説：制約

制約 は、列に入れられる値に付ける決まりです。制約に反する値を入れようとすると、Oracle はエラーにして登録を拒否します。これにより、「年齢が空の社員」「存在しない部署の社員」のような正しくないデータが入らないようになります。

制約	意味	この教材では
NOT NULL	値を必ず入れなければならない（空は許さない）	ID 以外のすべての列
PRIMARY KEY（主キー）	その行を特定するための列。同じ値は入れられず、空も許さない	DEPT_ID、EMP_ID
REFERENCES（外部キー）	参照先のテーブルの列に存在する値しか入れられない	EMPLOYEE の DEPT_ID → DEPARTMENT の DEPT_ID
CHECK	( ) の中に書いた条件を満たす値しか入れられない	GENDER は 1 か 2 だけ

空の値のことを NULL（ヌル）といいます。NULL は「0」や「空文字」とは違い、「値が入っていない」という状態を表します。NOT NULL は「NULL ではいけない」という意味です。NULL の扱いは、第6章と第10章で詳しく学びます。

制約に名前を付ける

CONSTRAINT 制約名 を前に付けると、制約に名前を付けられます。この教材では、主キー・外部キー・CHECK 制約に次の決まりで名前を付けています。

制約	名前の付け方	例
主キー	PK_ ＋ テーブル名	PK_EMPLOYEE
外部キー	FK_ ＋ テーブル名 ＋ _ ＋ 参照先のテーブル名	FK_EMPLOYEE_DEPARTMENT
CHECK	CK_ ＋ テーブル名 ＋ _ ＋ 列名	CK_EMPLOYEE_GENDER

名前を付けなくてもテーブルは作れますが、その場合 Oracle が SYS_C0011001 のような名前を自動で付けます。制約に反したときのエラーメッセージには制約の名前が表示されるので、自分で名前を付けておくと、どの制約に反したのかがすぐに分かります（第4章）。NOT NULL には名前を付けないのが一般的です。エラーメッセージに列名が表示されるからです。

列制約と表制約

制約の書き方には2つあります。

• 列制約：列の定義の後ろに、その列の制約を書く。3.2 の SQL はすべてこの書き方です
• 表制約：すべての列の定義を書いた後に、制約を まとめて 書く。どの列の制約かを ( ) の中に書く

社員テーブルを表制約で書くと、次のようになります（この SQL は実行しません。読み比べるためのものです）。

例（表制約で書いた社員テーブル。実行しない）

CREATE TABLE EMPLOYEE (
  EMP_ID      NUMBER(6),
  NAME        VARCHAR2(20 CHAR)  NOT NULL,
  GENDER      NUMBER(1)  NOT NULL,
  AGE         NUMBER(3)  NOT NULL,
  BIRTH_DATE  DATE       NOT NULL,
  DEPT_ID     NUMBER(4)  NOT NULL,
  ADDRESS     VARCHAR2(10 CHAR)  NOT NULL,
  CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMP_ID),
  CONSTRAINT FK_EMPLOYEE_DEPARTMENT FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPT_ID),
  CONSTRAINT CK_EMPLOYEE_GENDER CHECK (GENDER IN (1, 2))
);

制約	列制約の書き方	表制約の書き方
主キー	EMP_ID NUMBER(6) CONSTRAINT PK_EMPLOYEE PRIMARY KEY	CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMP_ID)
外部キー	DEPT_ID NUMBER(4) CONSTRAINT FK_… REFERENCES DEPARTMENT (DEPT_ID)	CONSTRAINT FK_… FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENT (DEPT_ID)
CHECK	GENDER NUMBER(1) CONSTRAINT CK_… CHECK (GENDER IN (1, 2))	CONSTRAINT CK_… CHECK (GENDER IN (1, 2))
NOT NULL	NAME VARCHAR2(20 CHAR) NOT NULL	（表制約では書けない。列制約で書く）

どちらの書き方でも、できるテーブルは同じです。表制約には、外部キーに FOREIGN KEY (列名) と書く、どの列の制約かを ( ) で示す、という違いがあります。2つ以上の列を組み合わせた主キー（たとえば「注文番号と行番号の組み合わせで1行を特定する」場合）は、列制約では書けないので表制約で書きます。この教材のテーブルは1列の制約だけなので、列の定義のそばに制約があって読みやすい、列制約で書いています。現場では両方の書き方を見かけるので、どちらも読めるようにしておきましょう。

3.6 解説：シーケンス

シーケンス は、1、2、3…と重ならない番号を順に払い出してくれるデータベースの部品です。CREATE SEQUENCE SEQ_EMP NOCACHE; は、「社員IDに使う番号を払い出すシーケンス SEQ_EMP を作る」という指定です。

• 番号は 1 から 1 ずつ増える：開始の番号と増やす幅は、何も書かなければ 1 になります
• NOCACHE を必ず付ける：付けないと、Oracle は番号を前もって20個まとめて用意しておき、データベースを再起動したときなどに、用意したまま使わなかった番号を捨てます。そのため、社員IDが 1, 2, 21 のように大きく飛ぶことがあります。NOCACHE を付けると、番号をまとめて用意しないので、このような飛び方をしなくなります
• 次の番号は シーケンス名.NEXTVAL で取り出す：第4章の INSERT で、SEQ_EMP.NEXTVAL と書いて社員IDに次の番号を入れます

主キーの番号を人が決めると、「次は何番か」を毎回調べなければならず、同時に2人が登録すると同じ番号を付けてしまうおそれがあります。シーケンスに任せれば、番号が重なることはありません。

【知識メモ】シーケンスとテーブルは別のもの

シーケンスは、テーブルとは独立した部品です。SEQ_EMP という名前から社員テーブル用だと分かりますが、Oracle がテーブルと結び付けているわけではありません。INSERT のときに SEQ_EMP.NEXTVAL と書くことで、初めて社員IDに使われます。

3.7 テーブルを消す・作り直す

テーブルの定義を間違えたときは、テーブルを DROP TABLE で消して作り直すのが確実です。DROP TABLE は、テーブルの定義と中のデータを すべて 消します。

ワークシート（XE_DBAPP）

-- 社員テーブルを先に削除する（部署テーブルを参照しているため）
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;

-- シーケンスも削除する
DROP SEQUENCE SEQ_EMP;
DROP SEQUENCE SEQ_DEPT;

消したら、3.2 の SQL をもう一度実行します。シーケンスを作り直すので、番号も 1 から振り直されます。この章では定義を間違えていなければ消す必要はありませんが、第4章でデータを登録し直したいときにも、この手順を使います。

【ご注意】削除する順番に注意

社員テーブルは外部キーで部署テーブルを参照しています。部署テーブルを先に削除しようとすると、「ORA-02449: 表の一意キーまたは主キーが外部キーに参照されています。」というエラーになります。参照している側（社員）から先に 削除してください。

【再確認】DROP TABLE は取り消せない

DROP TABLE を実行すると、テーブルの中のデータも一緒に消えます。第4章で学ぶ ROLLBACK でも元に戻せません。消す前に、本当にそのテーブルを消してよいか、テーブル名を確かめてから実行してください。現場では、消す前にデータの控え（バックアップ）を取るのが原則です。

3.8 うまくいかないときは

症状	考えられる原因と対処
「ORA-00955: すでに使用されているオブジェクト名です。」	同じ名前のテーブルやシーケンスがすでにあります。一度実行した SQL をもう一度実行したときに出ます。作り直すなら、3.7 の手順で消してから実行してください。
「ORA-00942: 表またはビューが存在しません。」	参照先のテーブルが無いか、名前を打ち間違えています。REFERENCES DEPARTMENT の前に CREATE TABLE DEPARTMENT が成功しているか、つづりが合っているかを確認してください。XE_SYSTEM のワークシートで実行していないかも確認してください。
「ORA-00907: 右カッコがありません。」	( と ) の数が合っていないか、列と列の間の , が抜けています。
「ORA-00902: データ型が無効です。」	データ型のつづりが間違っています（NUMBR、VARCHAR2 の 2 の抜け、CHAR の位置など）。
「ORA-00905: キーワードがありません。」	NOT NULL や PRIMARY KEY などのつづりが間違っています。
「ORA-00904: : 無効な識別子です。」	最後の列の後ろに余分な , があります。最後の列の後ろには , を付けません。
「ORA-02449: 表の一意キーまたは主キーが外部キーに参照されています。」	参照されている部署テーブルを先に消そうとしています。社員テーブルから先に消してください。
「接続」パネルの「表」にテーブルが出てこない	「表」を右クリックして「リフレッシュ」を選んでください。それでも出ないときは、XE_SYSTEM のワークシートで実行して system のスキーマにできている可能性があります。XE_SYSTEM の「表」を確認し、あればそちらで DROP TABLE してから、XE_DBAPP で作り直してください。

ほかのエラーは、付録B「よくあるエラーと対処法」 も参考にしてください。

3.9 まとめと確認問題

まとめ

• CREATE TABLE テーブル名 ( 列名 データ型 制約, … ); でテーブルを作る。参照される側（部署）を先に作る
• データ型は、整数の NUMBER(n)、文字列の VARCHAR2(n CHAR)、日付の DATE
• 制約は、NOT NULL（空を許さない）、PRIMARY KEY（主キー）、REFERENCES（外部キー）、CHECK（条件）。CONSTRAINT 名前 で名前を付ける
• 制約は、列の定義の後ろに書く 列制約 と、まとめて書く 表制約 のどちらでも書ける。複数の列にまたがる制約は表制約で書く
• CREATE SEQUENCE 名前 NOCACHE; で、1 から順に番号を払い出すシーケンスを作る
• DROP TABLE はテーブルとデータをすべて消し、取り消せない。参照している側から先に消す

この章で使った SQL は、付録C「SQL 早見表」 にまとめています。

確認問題

問1　VARCHAR2(20) ではなく VARCHAR2(20 CHAR) と書くのはなぜですか。

解答を見る

CHAR を付けないと長さがバイト数で数えられ、日本語は1文字が3バイトになることが多いので、20バイトには漢字が6文字ほどしか入らないためです。CHAR を付けると文字数で数えられ、日本語でも20文字まで入ります。

問2　社員テーブルの GENDER に 3 を入れようとすると、どの制約に反してエラーになりますか。

解答を見る

CHECK 制約 CK_EMPLOYEE_GENDER（CHECK (GENDER IN (1, 2))）に反します。

問3　部署テーブルと社員テーブルは、どちらを先に作らなければなりませんか。その理由も答えてください。

解答を見る

部署テーブル（DEPARTMENT）を先に作ります。社員テーブルの DEPT_ID が外部キーで部署テーブルを参照しているので、参照先が無いと社員テーブルを作れない（ORA-00942）ためです。

問4　次の列制約を、表制約の書き方に書き換えてください。
DEPT_ID NUMBER(4) CONSTRAINT PK_DEPARTMENT PRIMARY KEY

解答を見る

列の定義は DEPT_ID NUMBER(4), とし、すべての列の後に CONSTRAINT PK_DEPARTMENT PRIMARY KEY (DEPT_ID) と書きます。

問5　CREATE SEQUENCE SEQ_EMP NOCACHE; の NOCACHE を付ける理由を答えてください。

解答を見る

付けないと Oracle が番号を20個まとめて用意し、再起動などで使わなかった番号を捨てるため、社員IDが 1, 2, 21 のように大きく飛ぶことがあるからです。NOCACHE を付けると、まとめて用意しないのでこのような飛び方をしません。

---

前へ

第2章 データベースに接続する

次へ

第4章 データを登録する