DB/SQL/表の作成

Top/DB/SQL/表の作成

命名規則†

表の名前、または列の名前として使える文字列の条件は以下の通り。

  • 文字で開始すること
  • 1 文字から 30 文字であること
  • [A-Za-z0-9_$#] からなること
  • 同一ユーザの所有する他のオブジェクトの名前と重複がないこと
  • 予約語でないこと
↑

表の作成†

↑

基本†

  • 方法 
    -- こんなかんじ
create table hoge
    ( <column-name> <data-type> [, <column-name> <data-type> [, ...]] );

-- 具体的にはこう
create table hoge
    ( fuga number(1),
      piyo number(1),
      foo  varchar2(2),
      bar  varchar2(2) );

-- ほかの表をパクって作るときはこう
create table hoge (fuga, piyo)
    as select bar, baz
       from foo
       where bar > 5;

-- 使う表領域などを指定する
create table hoge ......
    tablespace fuga
    storage (
        initial 64k
        next 64k
    );
  • 補足
    • ほかの表をパクって作るときは、列名（上の例でいう fuga, piyo の部分）はなくてもいい
      • なしにした場合、hoge 表には bar 列と baz 列ができる
    • ほかの表をパクってきたときは、中身も一緒に持ってこられる
    • ほかの表をパクってきても、NOT NULL 以外の制約は適用されない
    • initial 句は初期エクステントのサイズの指定。表領域のエクステント管理が autoallocate の場合は、initial 句の値に最適なエクステントサイズが最低限必要な個数が自動で割り当てられる。uniform size の場合は uniform size で指定したサイズ単位で initial 句を満たす最低の個数が自動で割り当てられる
    • next 句は追加で割り当てていくエクステントサイズの指定。initial と同様、表領域のエクステント管理によって割り当てられ方が変わる
↑

データ型†

列の主なデータ型は以下の通り。

  • データ型
    データ型説明
    varchar2(<size>)可変長文字列
    char(<size>)固定長文字列
    number(<precision>, <size>)桁数、小数点以下桁数。<size> をマイナスにすると丸められる。<size> は指定しなくても良い
    date日付と時刻
    long可変長文字列
    timestamp [with [local] timezone]小数秒含む現在時刻
    interval year to month時刻の間隔を保存（よくわからん……）
↑

オプション†

↑

DEFAULT オプション†

列のデフォルト値を設定する

  • 方法 
    create table hoge
    ( fuga number(1)  default 0,
      piyo varchar(3) default 'foo');
↑

制約†

名前は必須ではないけれども、運用上つけたほうが良い。

なぜなら、これらの名前は運用上、または利用上、人間の目に触れる形で参照されることが多いから。

PRIMARY KEY 制約と UNIQUE 制約が定義された列には制約名と同じ名前の一意索引が作成されることから、システムによる命名では判読性に欠ける。あるいは、制約名で制約の有効と無効を切り替える際（SQL*Loader などから読み込むときなど）にも、システムによる命名では制約名の参照に手間がかかる。

↑

PRIMARY KEY 制約†

当該の列を主キーであると定義する。NULL 値は許されない（NOT NULL 制約が自動で付加される）。

列レベル、または表レベルで定義が可能。ただし複合主キーを定義する場合は表レベルのみ。

  • 方法 
    -- 列レベルでの定義
create table hoge
    ( fuga number(1) constraint hoge_pk primary key);

-- 表レベルでの定義
create table hoge
    ( fuga number(1),
      piyo number(1),
      constraint hoge_pk primary key (fuga, piyo));
↑

UNIQUE 制約†

当該の列が一意であることを保証する。NULL の入力は許可される。

列レベル、または表レベルで定義が可能。ただし複合一意キーを定義する場合は表レベルのみ。

  • 方法 
    -- 列レベルでの定義
create table hoge
    ( fuga number(1) constraint hoge_fuga_uk unique);

-- 表レベルでの定義
create table hoge
    ( fuga number(1),
      piyo number(1),
      constraint hoge_fugapiyo_uk unique (fuga, piyo));
  • 補足
    • 複合一意キーでも NULL は許可される。たとえば上の二個目の例で、以下の状態になっても制約違反にはならない
      fugapiyo
      1null
      2null
      null1
      nullnull
      nullnull
↑

NOT NULL 制約†

当該の列に NULL の格納を許可しない。

列レベルのみで定義が可能。

  • 方法 
    -- 列レベルでの定義
create table hoge
    ( fuga number(1) constraint hoge_fuga_nn not null);
↑

FOREIGN KEY 制約†

当該の列が外部キーであると定義する。

列レベル、または表レベルで定義が可能。ただし複合外部キーを定義する場合は表レベルのみ。

  • 方法 
    -- 列レベルでの定義
-- foo 表の bar 列を参照する
create table hoge
    ( fuga number(1) constraint hoge_fuga_fk foreign key references foo (bar));

-- 表レベルでの定義
-- foo 表の bar 列と baz 列を参照する
create table hoge
    ( fuga number(1),
      piyo number(1),
      constraint hoge_fugapiyo_fk foreign key (fuga, piyo)
          references foo (bar, baz));
  • 補足
    • その他のオプションとして以下の併記が可能
      オプション説明
      on delete cascade参照先の行が削除されると参照元の行も削除する
      on delete set null参照先の行が削除されると参照元の行を NULL にする
    • 上記オプションを指定しない場合は、先に参照元の行を削除しない限り、参照先の行は削除できなくなる（テーブルごとも消せない）
    • 参照先の列は一意である必要がある（PRIMARY KEY もしくは UNIQUE）
↑

CHECK 制約†

当該の列に格納する値を制限する。

  • 方法 
    -- 列レベルでの定義
create table hoge
    ( fuga number(1) constraint hoge_fuga_min check (fuga > 5));

-- 表レベルでの定義
create table hoge
    ( fuga number(1),
      piyo number(1),
      constraint hoge_fuga_min check (fuga > 5));
↑

表の変更†

既存の表の定義を変更する。

  • 方法 
    -- 新しい列の追加
alter table hoge add (fuga number(1));

-- 新しい列にデフォルトを定義
alter table hoge add (fuga number(1) default 0);

-- 既存の列を変更
alter table hoge modify (fuga number(1));

-- 既存の列を削除
alter table hoge drop fuga;
  • 補足
    • 変更後に一切の矛盾が生じない場合に限り変更ができる
      • たとえば、すでに 123 が格納されている number(3) の列を number(1) には変更はできない
      • number(3) の列がすべて NULL だったり、1 桁の数字しか入っていない状態であれば number(1) には変更できる
↑

表の削除†

  • 方法 
    drop table hoge;
  • 補足
    • DDL 文なので、保留中のトランザクションを含めて自動でコミットされる（ロールバックできない）
    • ビューとシノニムは残るが無効になる
↑

表の定義の確認†

SQL*Plus のコマンドなので汎用の SQL ではない。

実行に権限は必要ない。というか、権限がなくても desc はできる。

  • 方法 
    desc <tablename>
Last-modified: 2011-11-07 (月) 14:31:49