Oracle『テーブルを定義する』データベースに表を作成する

Oracle『テーブルを定義する』データベースに表を作成する Oracle SQL入門
2023.04.03

【本サイトではGoogleアドセンス、または、アフィリエイト広告を利用しています。】

  1. スキーマにテーブルを定義しよう
  2. テーブル定義をする際に考慮すべきポイント
    1. テーブルを作成するSQL（CREATE TABLE）
      1. テーブル名、カラム名に日本語を使うべきか？
    2. データ型とは？
      1. データ型の定義方法
      2. VARCHARじゃなくてVARCHAR2？
      3. CHARとVARCHAR2の使い分けは？
      4. DATEとTIMESTAMPの使い分けは？
    3. 主キーとは？
    4. 制約とは？
    5. テーブル全体に制約を設定する方法
    6. コメントとは？
  3. テーブルの定義方法　まとめ

スキーマにテーブルを定義しよう

データベースの表領域とスキーマ（ユーザー）が作成できたら、いよいよテーブルの作成です。

テーブルは、作成したユーザーでログインして登録する必要があります。

なぜなら、テーブルは、SQLを実行したユーザーの持つスキーマに作成されるからです。

　パソコンを操作するロボット

ということで、クライアントツールにログインする際は、該当のスキーマを使うようにしてください。

テーブルの詳細については、下記で説明していますので、イメージがつかない人はあわせて読んでみてください！

【図解】『データベースの基礎』RDBMS（リレーショナルデータベース）の構造
データベースの基礎について説明します。RDBMS（リレーショナルデータベース管理システム）の基本的な構造とそれぞれの関係性について、図を使ってわかりやすく解説します。
it-road.com
2024.04.13

テーブル定義をする際に考慮すべきポイント

テーブルを作成する前に、どんな表の構成にするのかを考える必要があります。

どんなカラムがあって、どんなデータが入るのか、また、複数のテーブルを作成する場合は、
テーブル同士がどのような関係性になるのかを考えておく必要があります。

例えるなら、部屋に散らかった物を整理する時と同じです。

　家が散らかっている人

どの箱にどのジャンルの物を入れようかとか、後で取り出す時にできるだけわかりやすい方がいいなとか、
いろいろ考えて部屋を片付けていきますよね？

同じ考え方でデータも整理すると上手にテーブルが作成できます。

具体的には、テーブル作成時に以下の点を考慮します。

  • テーブルにどんなデータを入れるか？
  • テーブルが持つカラム（列）
  • 各カラムのデータ型
  • 主キー
  • 制約

見たことないワードがあると思いますので順番に説明します。

テーブルを作成するSQL（CREATE TABLE）

まずは、テーブルを作成するSQLです。
テーブルは「CREATE TABLE」という構文で作成します。

CREATE TABLE [テーブル名] (
    [カラム名１] [データ型] [制約],
    [カラム名２] [データ型] [制約], 
    [カラム名３] [データ型] [制約],
    ・・・,
);

例えば、クラスにいる生徒のデータを以下のような表で管理するとします。

出席番号名前5教科の平均点データ登録日
・・・
・・・

上記の例から考えると、SQLは以下のようになります。

CREATE TABLE STUDENT (
    STUDENT_NO CHAR(2),
    NAME VARCHAR2(30) NOT NULL,
    AVG_SCORE NUMBER(6, 3),
    REGIST_DATE TIMESTAMP NOT NULL
);

テーブル名やカラム名には文字数制限がありますが、予約語以外のワードであれば自由に決めることができます。

また、データ型や制約についても上記の例の通りに定義する必要はありません。
自分がどんな形でデータを入れたいのかにあわせて定義をしていきます。

テーブル名、カラム名に日本語を使うべきか？

名前は自由に決められるとお伝えしましたが、名前にどんな文字を使うのかには少し注意点があります。

Oracleでは、名前を決める時に、アルファベットでも日本語でも定義できるように対応しています。

しかし、もともと英語圏のソフトウェアなので、日本語での定義は後付けで行えるようになっています。

Oracleでは定義できても、データベースを使う側のソフトウェアが
日本語で定義されたテーブルのデータ取得に対応しているとは限りません。

また、ソフトのバージョンが上がった時に、
リリース時点でどこまで日本語の定義が適用されているのかも不明です。

私たちには意味がわかりやすく便利ですが、基本的にはアルファベットで定義するようにしましょう。

また、ローマ字ではなく、見たことのない英単語でもなく、
中学高校で習うぐらいの英単語を使う方が読みやすく意味も直感的に理解しやすいです。

データ型とは？

それでは、次に、データ型とは何か？
データ型をどのように決めていくのか見ていきましょう。

テーブルの持つカラムには、それぞれデータ型を定義する必要があります。

データ型とは、数字や文字列などのデータの種類です。

Oracleのデータ型で主要なものは以下です。

データ型種類説明
CHAR文字文字数が決まっている値を格納できます。
VARCHAR2文字文字数がデータによって違う値を格納できます。
NUMBER数値正の整数、負の整数、小数を格納できます。
DATE日時日付と時刻（秒まで）を格納できます。
TIMESTAMP日時日付と時刻（ミリ秒まで）を格納できます。

Oracleのデータ型には他にもいろいろな種類がありますが、
基本的にはここにあるデータ型だけで、おおよその情報は整理できるはずです。

できない場合は、テーブルの設計、システムの要件や仕様、
プログラムのロジックのいずれかに無理があるかもしれません。

もしそれでもここにあるデータ型で整理できないという状況があれば、
該当するデータ型を以下から探してみましょう。

SQL言語リファレンス
docs.oracle.com

データ型の定義方法

先ほどのCREATE TABLE文の例にある定義をもう一度見てください。

    STUDENT_NO CHAR(2),

データ型の後ろに「()」と数字が書かれています。

これは、カラムに入る最大バイト数を指します。数値の場合は桁数になります。

Oracleでは、テーブルの各カラムに何バイト（何桁）のデータを入れるのか、
ということをテーブル作成時に定義しておく必要があります。

カラムとデータ型を決める時に、どんな情報が入ってくるのかを推測してバイト数を決定しましょう。

CHARやVARCHAR2の場合は「(n)」でバイト数nと定義します。
日本語のデータを入れる場合、文字数とバイト数は違うので定義の際に注意が必要です。

文字コードにもよりますが、日本語１文字＝３バイトとして余裕を持って定義しましょう。

NUMBERの場合は「(x)」でx桁の整数、「(x, y)」でx桁、小数以下y桁の数値となります。

ここで注意して欲しいのは、xは整数の桁数ではなく数値の桁数です。
例えば、「15.3」という小数を含む数字を扱いたい場合は、「(2,1)」ではなく、「(3,1)」として定義します。

TIMESTAMPやDATEなどの日時型には指定不要です。

VARCHARじゃなくてVARCHAR2？

VARCHAR「2」って、なんでいきなり続編から始まるんだ！

と思った人いるんじゃないでしょうか？

そう、今はVARCHARではなく、VARCHAR2を使うのが基本なのです。

なぜなら、VARCHARは古いバージョンのデータ型なので、
もう使わないようにとOracle社が発信しているからです。

　賢いプログラム

なので、Oracleで文字列を扱う時は新しい仕様の「VARCHAR2」を使うということで覚えておきましょう。

CHARとVARCHAR2の使い分けは？

CHARとVARCHAR2の使い分けですが、CHARはデータのステータスを文字や数字で表現したり、
ユーザーを内部的に割り振る識別番号などシステムが内部的に使う情報として定義することが多いです。

VARCHAR2は名前や住所などのような、ユーザーが画面から入力した値を保持する為に定義することが多いです。

CHARを使う際の注意ですが、割り振っていたIDの数が枯渇して桁数を増やすとなると、
例えば、「123」番の人は「0123」番にするのか、数字だけでなく、
アルファベットを使うのかなど、細かい話が出てきます。

データベースの定義更新をするとなると、かなり大変な作業になるので、
将来的に桁数が足りなくならないかなど事前に考慮しておきましょう。

DATEとTIMESTAMPの使い分けは？

DATEとTIMESTAMPの使い分けは、情報をミリ秒まで記録しておく必要があるかどうかで判断します。

例えば、データの登録時間を記録として保持するのであれば、
DATEよりもTIMESTAMPで持つ方が履歴としての精度が高くなりシステムとしての品質も上がります。

主キーとは？

テーブルの概念の１つに主キーというものがあります。

主キーとは、テーブルにあるデータを一意に識別する為の列のことです。

プライマリーキー（primary key）とも呼びます。

主キーは、以下のSQLでテーブルに定義できます。

ALTER TABLE [テーブル名] ADD CONSTRAINT [主キー制約名] PRIMARY KEY (カラム１, カラム２, ・・・);

先ほどの例で、出席番号を主キーとして、主キー制約名「STUDENT_PK」で定義すると以下のようになります。
（制約については後述します。また、名前は任意で付けられます。）

ALTER TABLE STUDENT ADD CONSTRAINT STUDENT_PK PRIMARY KEY (STUDENT_NO);

一意に識別するとは、データを区別できるということです。

つまり、テーブルに２つ以上のデータがある時、
それぞれ主キーの内容が異なっている必要があるということになります。

また、主キーは１列だけではなく、２列以上組み合わせて設定することもできます。

テーブルに主キーを必ず設定する必要はありませんが、つけないことで、
まったく同じ内容データを登録できてしまうので、データを整理しにくくなります。

また、データをSQLで抽出する際も重複したレコードが出てくるなど、
意図しない結果が出てくる可能性が出てきます。

基本的には主キーを付けるということで覚えておきましょう！

制約とは？

CREATE TABLEの例でもう１つ見て欲しい部分があります。

    NAME VARCHAR2(30) NOT NULL,

最後に「NOT NULL」と書かれています。
これは、制約と呼ばれるものの１つです。

制約とは、データを登録する際の条件のことです。
制約の持つルールに沿わないデータは入れることができません。

制約は「NOT NULL」以外にもいろいろあります。

制約説明
PRIMARY KEY主キーとして扱う。
NOT NULLカラムに必ず値を入れさせる。
UNIQUE各レコードが一意の値を持つよう制限する。
DEFAULTデータの初期値を決める。
REFERENCES親テーブルが持たない値を入れられないようにする。
CHECK指定した条件に一致する場合のみ登録を許可する。

例では、名前とデータ登録日を必須入力にしたいので、各列にNOT NULLを設定しています。

ちなみに、PRIMARY KEY制約は、先ほどALTER文で後から設定しましたが、
下記のようにCREATE TABLE文に指定して一気に主キーを定義することもできます。

CREATE TABLE STUDENT (
    STUDENT_NO CHAR(2) PRIMARY KEY,
    NAME VARCHAR2(30) NOT NULL,
    AVG_SCORE NUMBER(6, 3),
    REGIST_DATE TIMESTAMP NOT NULL
);

但し、上記の方法では、制約名がつけられないことと、複数組み合わせての主キー設定ができません。

テーブル全体に制約を設定する方法

カラムごとに制約を設定する方法とは別に、以下の書き方でテーブル全体に制約を設定することもできます。

CREATE TABLE [テーブル名] (
    [カラム名１],
    ・・・,
    CONSTRAINT [制約名] [制約] ([対象カラム])
);

制約の内容はカラムの制約と同じですが、例えば「NOT NULL」などはこの方法で設定することができません。

先ほどの主キー設定の例でいえば、以下のように書くことで主キーを設定できます。
また、この場合、制約名の指定と複数の主キー設定が可能です。

CREATE TABLE STUDENT (
    STUDENT_NO CHAR(2),
    NAME VARCHAR2(30) NOT NULL,
    AVG_SCORE NUMBER(6, 3),
    REGIST_DATE TIMESTAMP NOT NULL,
    CONSTRAINT STUDENT_PK PRIMARY KEY (STUDENT_NO)
);

と、いろいろと定義の仕方がありますが、どれが良いということは特にないので、
自分がわかりやすいものを選びましょう。

コメントとは？

コメントとは、テーブルや列に付加する説明文のことです。

先ほど、テーブル名を「STUDENT」、列名を「STUDENT_NO」などとしましたが、
他の人が見た時に、これだけでは正式な名前や用途がわかりません。

ということで、説明書きをコメントで付けることができるようになっています。

この時、テーブルを定義した際に使った名前を物理名、コメントとして付加した設計上の名前を論理名と呼びます。

コメントを付加するSQLは以下です。

COMMENT ON TABLE [テーブル名] IS '[コメント]';

COMMENT ON COLUMN [テーブル名].[カラム名] IS '[コメント]';

カラムにコメントを付ける際は、どのテーブルのどのカラムかを指定する必要がありますが、
SQLではテーブル名とカラム名を「.」（ドット）でつなげて指定します。

また、名前については「’」（シングルクォーテーション）で囲って文字列を指定します。
この時使う「’」のことを文字リテラルと呼びます。

生徒テーブルにコメントをつけると以下のようなSQLになります。

COMMENT ON TABLE STUDENT IS '生徒テーブル';

COMMENT ON COLUMN STUDENT.STUDENT_NO IS '出席番号';
COMMENT ON COLUMN STUDENT.NAME IS '名前';
COMMENT ON COLUMN STUDENT.AVG_SCORE IS '5教科の平均点';
COMMENT ON COLUMN STUDENT.REGIST_DATE IS 'データ登録日';

コメントについては、日本語を使ってもSQLの構文自体には影響がないので、
読み取りやすいように、テーブル作成時にあわせてつけておくことをおすすめします。

テーブルの定義方法　まとめ

Oracleでテーブルを定義する方法について以下説明しました。

  • テーブル定義のポイント
  • テーブルの作成（CREATE TABLE）
  • テーブル名、カラム名の決め方
  • データ型の種類
  • データ型の定義方法
  • VARCHARとVARCHAR2
  • CHARとVARCHAR2の使い分け
  • DATEとTIMESTAMPの使い分け
  • 主キーについて
  • 制約について
  • 制約の定義方法
  • コメントについて（物理名と論理名）

テーブルを定義して、データが入った状態から定義を変更するとなると、
入っているデータごと見直す必要があり、かなり大変な作業になります。

他のテーブルとの関係性や、入ってくるデータの内容などを推測して
できるだけ変更の必要がないテーブル定義をすることが品質を上げるコツです。

データベース設計の第一歩としてテーブル定義はしっかり行いましょう！

タイトルとURLをコピーしました