2.テーブルの操作

2.1テーブルの作成(CREATE TABLE)

テーブルの作成には CREATE TABLE 文を用います。CREATE TABLE 文の構文は次の通りです。

1
2
3
4
5
CREATE TABLE 「tablename」(  
column1 type1 [not null] [PRIMARY KEY],  
column2 type2 [not null],  
column3 type3,  
...);

下記のSQL文の機能と同じように見られます。

1
2
3
4
5
6
7
CREATE TABLE 「tablename」(
column1 type1 [not null],
column2 type2 [not null],
column3 type3,
...,
PRIMARY KEY (column1)
);

説明： CREATE TABLE の後にテーブル名を、その後ろの部分にカッコで囲んでフィールド名称、つまり列名とデータ型を設定します。 CREATE TABLE 文は行のない空のテーブルを作成する構文です。CREATE TABLE 文は基本的に指定された順番に並んだ名前の付いた列の集合を説明するものとして、テーブル名を定義します。また、列のデータ型とサイズも定義します。テーブルは列を一つ以上持っていなければなりません。

例えば、下のような表を作成するには、sample-02-1のような SQL を記述します。ここで、受注番号と受注個数は整数、得意先コードと商品コードは固定長文字列とします。

表：受注表
受注番号 得意先コード 商品コード 受注個数

sample-02-1:

1
2
3
4
5
6
7
8
/* 受注表の定義 */
CREATE TABLE 受注表
(
   受注番号     INTEGER NOT NULL,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER
) ;

2.2テーブルの変更(ALTER TABLE)

テーブルの変更は ALTER TABLE 文で行います。既存のテーブルの定義を変更します。ALTER TABLE は次の機能を備えています。

1.テーブルに列を追加する
2.テーブルから列を削除する
3.テーブルにテーブル制約を追加する
4.テーブルからテーブル制約を削除する
5.列にデフォルト値を追加する
6.列からデフォルト値を削除する

テーブル制約 (CONSTRAINT) とは、テーブルの列に入力できる値を制限するテーブル定義の一部のことです。具体的にどのように制約するのかについては後述します。また、デフォルト値とは、テーブルへの INSERT INTO 文に列の値が指定されなかった場合に、そのテーブルの列に自動的に挿入される値のことです。

ALTER TABLE 文の構文は次の通りです。
➊列の追加

1
ALTER TABLE 「tablename」 ADD [COLUMN] 「columnname」「datatype」[NOT NULL] [DAFAULT]「value」

➋列を削除
注：SQLiteで実行することができません

1
ALTER TABLE 「tablename」 DROP [COLUMN] 列名 [ RESTRICT | CASCADE ]

➌テーブル制約の追加
注：SQLiteで実行することができません

1
ALTER TABLE 「tablename」 ADD テーブル制約の定義

➍テーブル制約の削除
注：SQLiteで実行することができません

1
ALTER TABLE 「tablename」DROP CONSTRAINT テーブル制約の定義 [ RESTRICT | CASCADE ]

➎デフォルト値の追加
注：SQLiteで実行することができません

1
ALTER TABLE 「tablename」 ALTER [COLUMN] 列名 SET DEFAULT デフォルト値

➏デフォルト値の削除
注：SQLiteで実行することができません

1
ALTER TABLE 「tablename」 ALTER [COLUMN] 列名 DROP DEFAULT

上の構文の中に示した [ COLUMN ] は省略可能です。ある WEB ページの説明には、「[ COLUMN ] はノイズであり、省略可能です」と書かれていました。 　　[ RESTRICT | CASCADE ] とは、RESTRICT 句と CASCADE 句のいずれかを記述するという意味です。RESTRICT 句が指定された場合は、指定した表がビューや整合制約により参照されたものである場合は変更は行われません。CASCADE 句が指定された場合は、列を参照するビューは変更されず、それらは破棄されます。

例えば：受注表に納品日という列を追加したい場合には、SQL で sample-02-2 のように書きます。納品日のデータ型は固定長文字列とします。

sample-02-2:

1
2
/* 納品日列の追加*/
ALTER TABLE 受注表 ADD 納品日 CHAR(8) ;

注：列の追加時、「NOT NULL」を設定する時、[DAFAULT]を設定しなければなりません。

sample-02-3:

1
ALTER TABLE 受注表 ADD 納品日 CHAR(8) NOT NULL DEFAULT 20130918 ;

データの再定義が必要な場合に ALTER TABLE 文は必須ですが、できるだけ ALTER TABLE 文に頼らず設計することが望ましいとされています。というのは、すでに使用されているテーブルの構造を変更することは大変危険な行為であり、テーブルのビューが正しく機能しなくなる、アプリケーションが誤動作を引き起こす、機能を停止するなどの可能性があるからです。データベースの設計の段階で、現在のニーズだけでなく将来的なニーズも視野に入れてテーブルの設計に取り込み、ALTER TABLE 文はあくまでも最後の手段として利用する事が大切です。

2.3テーブルの削除(DROP TABLE)

テーブルの削除は、実際には 2段階のプロセスからなります。まず DELETE 文を使って、テーブルのデータを空にします。次に DROP TABLE 文を使って、テーブルの定義を破棄します。つまり、DROP TABLE は空のテーブルを削除するものであり、データを破棄するものではないということです。DROP TABLE 文の構文は次の通りです。

1
DROP TABLE 「tablename」 [ RESTRICT | CASCADE ] ;
  • RESTRICT 句が指定された場合は、指定した表がビューや整合制約により参照されたものである場合は削除は行われません。
  • CASCADE 句が指定された場合は、ビューや整合制約も削除されます。
    注：SQLiteで、RESTRICT | CASCADEを設定した、実行することができません

sample-02-4:

1
  DROP TABLE 受注表 ;

2.4. データ値の制約%

SQL の表定義ではデータ値に制約を持たせることで、登録されるデータが常に正しい状態を保つことができます。制約には「列制約」と「テーブル制約」という 2つの基本制約があります。両者の違いは、列制約が列のみに適用されるのに対し、テーブル制約が列のグループに適用されるということです。

CREATE TABLE 文では、列定義のデータ型の後ろに列制約を追加し、最後にカンマを付けます。テーブル制約は、テーブル定義の最後の列定義の後ろに配置し、最後に閉じカッコをつけます。制約を追加して拡張した CREATE TABLE 文の構文は次の通りです。

2.4.1. 主キーの指定 (PRIMARY KEY)

主キーとは、ベーステーブルの各行を一意に識別するための 1つ以上の列のグループのことです。主キーは NULL を持たず、一意であることを保障するために制約が適用されます。ちなみに、ベーステーブルとは他のテーブルから抽出されたデータが含まれないテーブルのことです。

次の受注表の受注番号に主キーを指定する場合、SQL では PRIMARY KEY という列制約を用いて sample-02-5 のように書きます。

表：受注表
受注番号 得意先コード 商品コード 受注個数

sample-02-5:

1
2
3
4
5
6
7
8
/* 主キーの指定 */
CREATE TABLE 受注表
(
   受注番号     INTEGER PRIMARY KEY ,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER
);

テーブル制約を用いることで、主キーを 2つの列に指定することもできます。次の価格表のように、販売店と製品の組合せから一意に価格が決まる表の場合、SQLでは sample-02-6のように記述します。

表：価格表
販売店コード 商品コード 価格

sample-02-6:

1
2
3
4
5
6
7
8
/* 主キーの指定 */
CREATE TABLE 価格表
(
   販売店コード CHAR(4) ,
   商品コード   CHAR(4) ,
   価格         INTEGER ,
   PRIMARY KEY(販売店コード , 商品コード)
) ;

2.4.2. 一意性制約 (UNIQUE)

テーブルの作成時に、列に UNIQUE 列制約を設定すると、データベースはすでに他の行の同じ列に存在する値の設定を拒否することができます。UNIQUE 制約は、次の点で PRIMARY KEY 制約と異なっています。

PRIMARY KEY 制約は、あるテーブルの 1つの列または複数の列に1度だけ使用できるが、UNIQUE 制約は何度でも使用できる。 PRIMARY KEY 制約を持つ列には NULL が含まれないが、UNIQUE 制約を持つ列には NULL が含まれる可能性がある。 商品表の商品コードに主キーを、商品名に UNIQUE 制約を指定する場合、SQL では sample-02-7 のように書きます。

表：商品表
商品コード 商品名 商品単価

sample-02-7:

1
2
3
4
5
6
7
/* UNIQUE制約の指定 */
CREATE TABLE 商品表
(
  商品コード  INTEGER PRIMARY KEY ,
  商品名      CHAR(16) UNIQUE,
  商品単価    INTEGER
) ;

このように、UNIQUE 列は PRIMARY 列と同じテーブルに宣言することができます。 また、UNIQUE 制約はテーブル制約とすることで、複数の列の組合せの一意性を保つことができます。各納品日にそれぞれの得意先には一種類の商品しか受け付けないとすると、得意先コードと納品日の組合せは一意でなければなりません。このとき SQL では sample-02-8　のように記述します。

表：受注表
受注番号 得意先コード 商品コード 受注個数 納品日

sample-02-8:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
/* UNIQUE制約の指定 */
CREATE TABLE 受注表
(
  受注番号     INTEGER PRIMARY KEY ,
  得意先コード CHAR(5) ,
  商品コード   CHAR(4) ,
  受注個数     INTEGER ,
  納品日       CHAR(8) ,
  UNIQUE (得意先コード, 納品日)
) ;

2.4.3. NULL を締め出す制約 (NOT NULL)

CREATE TABLE 文では、NOT NULL 制約を使用することで、列に NULL が許可されるのを防ぐことができます。この制約は列に対してのみ使用できます。 次の商品表の商品名に NOT NULL 制約を指定する場合は、SQL では sample-02-9 のように書きます。

表：商品表
商品コード 商品名 商品単価

sample-02-9:

1
2
3
4
5
6
7
/* NOT NULL制約の指定 */
CREATE TABLE 商品表
(
   商品コード  INTEGER PRIMARY KEY ,
   商品名      CHAR(16) UNIQUE NOT NULL ,
   商品単価    INTEGER
) ;

2.4.4. 列値の確認(CHECK)

例えば入力したデータが正しい範囲を逸脱していないか、正しいフォーマットになっているかなど、テーブルに入力するデータを必要に応じて制約を適用したい場合には、CHECK 制約を利用します。CHECK 制約を利用すれば、テーブルに入力されるデータが受理されるにあたって、満たしていなければならない条件を定義できます。

列を特定の値に制限することで、入力ミスを防ぐ事が出来ます。列制約として CHECK 制約を使用し、次の受注表で、受注個数は 10個以上でしか受け付けない場合などは、 sample-02-10 のように SQL を記述することで、列に入力される値を制限することができます。

表：受注表
受注番号 得意先コード 商品コード 受注個数

sample-02-10:

1
2
3
4
5
6
7
8
/* CHECK制約による入力値の制限 */
CREATE TABLE 受注表
(
   受注番号     INTEGER PRIMARY KEY ,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER CHECK(受注個数 >= 10)
) ;

CHECK 制約はテーブル制約として利用することもできます。行の複数の列を条件に加えたい場合に便利です。受注表で商品コードが 0003 の場合のみ、受注個数が 10個以下でも許可されるように指定する場合には、SQLでは sample-02-11 のように記述します。

表：受注表
受注番号 得意先コード 商品コード 受注個数

sample-02-11:

1
2
3
4
5
6
7
8
9
/* CHECK制約による入力値の制限 */
CREATE TABLE 受注表
(
   受注番号     INTEGER PRIMARY KEY ,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER ,
   CHECK(受注個数 >= 10 OR 商品コード = '0003')
) ;

2.4.5. 制約の名前付けと削除

制約には名前を付けることが可能です。名前を付けることにより、制約を破棄することができるようになります。先程の CHECK 制約を例に制約に名前を付けます。

表：受注表
受注番号 得意先コード 商品コード 受注個数

sample-02-12:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
/* 制約の名前付け */
  CREATE TABLE 受注表
  (
     受注番号     INTEGER PRIMARY KEY ,
     得意先コード CHAR(5) ,
     商品コード   CHAR(4) ,
     受注個数     INTEGER ,
     CONSTRAINT ENTERCHECK
     CHECK(受注個数 >= 10 OR 商品コード = '0003')
  ) ;

上の例では制約の名前は ENTERCHECK です。この制約を受注表から削除するには、ALTER TABLE 文を用いて次のように記述します。
注：SQLiteで、実行することができません。

表：受注表
受注番号 得意先コード 商品コード 受注個数

sample-02-13:

1
2
/* 制約の名前を削除 */
ALTER TABLE 受注表 DROP CONSTRAINT ENTERCHECK ;

2.4.6. デフォルト値の割り当て

デフォルト値とは、テーブルへの INSERT 文に列の値が指定されなかった場合に、そのテーブルの列に自動的に挿入される値のことです。厳密に言えば、デフォルト値の割り当ては制約ではありません。ユーザが入力値を制限せず、ユーザが値を入力しなかった場合にどうなるかを指定するものに過ぎないからです。しかし、両者を定義するための手続きは非常によく似ているので、ここで説明しておきます。

商品表で商品単価には 2,000 円のものが非常に多く、デフォルト値として 2000 を割り当てたい場合、SQL では sample-02-14 のように書きます。

表：商品表
商品コード 商品名 商品単価

sample-02-14:

1
2
3
4
5
6
7
/* DEFAULT の割り当て */
 CREATE TABLE 商品表
 (
    商品コード  INTEGER NOT NULL PRIMARY KEY ,
    商品名      CHAR(16) UNIQUE,
    商品単価    INTEGER DEFAULT 2000
 ) ;

デフォルト値と NULL に関する規則を簡単にまとめておきます。

Ⅰ.列に NOT NULL 制約も DEFAULT 句も設定されていない場合、そのテーブルへの INSERT がその列に値を提供しないと、列には NULL が挿入される。 Ⅱ.列に NOT NULL 制約が適用されているいないに関わらず、列に DEFAULT 句が設定されている場合、そのテーブルへの INSERT がその列の値を提供しないと、列にはあらかじめ指定されたデフォルト値が挿入される。 Ⅲ.列に NOT NULL 制約が適用され、デフォルト値が指定されていない場合、そのテーブルへの INSERT は必ず列の値を提供しなければならない。さもなければ INSERT はエラーとして拒否される。

2.5. 参照整合性と外部キー

2.5.1. 外部キーと親キー

テーブルのある列の値が 1つ残らず、他のテーブルの列に存在しなければならない場合、前者の列は後者の列を参照するといいます。これは 2つの列の意義に直接的な関係があることを示します。 次の 3つの表を例にして具体的に説明します。

説明：
受注表、得意先表、商品表の 3つの表を想定します。得意先表では得意先コードにより得意先名が、商品表では商品コードから商品名とその単価が分かります。受注表の得意先コード列と商品コード列が示しているように、受注表の受注番号につき得意先名と商品名は一つに絞られます。したがって、受注表の得意先コード列及び商品コード列は外部キーであり、それを参照する得意先表の得意先コード列と商品表の商品コード列は親キーとなります。

外部キーはどのテーブルのどの行も、親キーを持つテーブルのたった 1つの行を参照することを示します。ただし、複数の外部キーの値が同じ親キーの値を参照する可能性があるため、親キーは一意性を確立する制約、すなわち PRIMARY KEY 制約または UNIQUE 制約を持っていなければなりません。一般的には前者になります。

2.5.2. FOREIGN KEY 制約

SQL は、FOREIGN KEY 制約による参照整合性をサポートしています。この制約は、外部キーと親キーに参照整合性の原則を守らせるために、ユーザがデータベースに入力できる値を制限します。FOREIGN KEY 制約の適用には、親キーにまだ存在しない値をはじくように、外部キー列を制限するという効果があります。

FOREIGN KEY は他の制約と同じように CREATE TABLE 文 (または ALTER TABLE 文) に使用します。FOREIGN KEY 制約には、参照する親キーを指定します。テーブル制約または列制約となり、テーブルの場合は複数の列を 1つの外部キーとして使用することができます。

FOREIGN KEY 制約の構文は次の通りです。

➊テーブル制約

1
FOREIGN KEY 列リスト REFERENCES 親テーブル(列リスト) ;

➋列制約

1
REFERENCES 親テーブル(列リスト) ;

構文を見て分かるように、列制約では FOREIGN KEY というワードが含まれていないので、REFERENCES 制約とも呼ばれています。ここで、受注表の得意先コードと商品コードに FOREIGN KEY を設定する時の SQL を sample-02-15 に示します。

表：受注表
受注番号 得意先コード 商品コード 受注個数 納品日

sample-02-15:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
/* FOREIGN KEYの設定 */
/*テーブル制約*/
  CREATE TABLE 受注表
  (
     受注番号      INTEGER PRIMARY KEY ,
     得意先コード  CHAR(5) ,
     商品コード    CHAR(4) ,
     受注個数      INTEGER ,
     納品日        CHAR(8) ,
     FOREIGN KEY (得意先コード) REFERENCES 得意先表(得意先コード) ,
     FOREIGN KEY (商品コード) REFERENCES 商品表(商品コード)
  ) ;

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
/* FOREIGN KEYの設定 */
  /*列制約*/
  CREATE TABLE 受注表
  (
     受注番号      INTEGER PRIMARY KEY ,
     得意先コード  CHAR(5) REFERENCES 得意先表(得意先コード) ,
     商品コード    CHAR(4) REFERENCES 商品表(商品コード) ,
     受注個数      INTEGER ,
     納品日        CHAR(8)
  ) ;

テーブル制約を用いて、2つの列を外部参照することもできます。今、次のような製品取扱表と販売店取扱表があり、販売店取扱表のメーカー列と製品列が製品表のメーカー列と製品列を外部参照している場合、すなわち販売店取扱表にデータを入力する際に製品表に登録されたメーカーと製品の組合せの入力以外を許可しない場合、SQL では sample-02-16 のように記述します。

表：製品取扱表
メーカー 製品
表：販売店取扱表
販売店 メーカー 製品

sample-02-16

1
2
3
4
5
6
7
/* FOREIGN KEYの設定 */
  CREATE TABLE 製品取扱表
  (
     メーカー CHAR(10) ,
     製品     CHAR(10) ,
     PRIMARY KEY (メーカー , 製品)
  ) ;

1
2
3
4
5
6
7
8
  CREATE TABLE 販売店取扱表
  (
     販売店   CHAR(10) NOT NULL ,
     メーカー CHAR(10) NOT NULL ,
     製品     CHAR(10) NOT NULL ,
     PRIMARY KEY (販売店 , メーカー , 製品) ,
     FOREIGN KEY (メーカー , 製品) REFERENCES 製品取扱表(メーカー , 製品)
  ) ;

2.6. SQL文

  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
CREATE TABLE 受注表
(
   受注番号     INTEGER NOT NULL,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER
) ;

ALTER TABLE 受注表 ADD 納品日 CHAR(8) ;

ALTER TABLE 受注表 ADD 納品日2 CHAR(8) NOT NULL DEFAULT 20130918 ;

DROP TABLE 受注表;

DROP TABLE 受注表;
CREATE TABLE 受注表
(
   受注番号     INTEGER PRIMARY KEY ,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER
);


CREATE TABLE 価格表
(
   販売店コード CHAR(4) ,
   商品コード   CHAR(4) ,
   価格         INTEGER ,
   PRIMARY KEY(販売店コード , 商品コード)
) ;

CREATE TABLE 商品表
(
  商品コード  INTEGER PRIMARY KEY ,
  商品名      CHAR(16) UNIQUE,
  商品単価    INTEGER
) ;

DROP TABLE 受注表;
CREATE TABLE 受注表
 (
 受注番号     INTEGER PRIMARY KEY ,
 得意先コード CHAR(5) ,
 商品コード   CHAR(4) ,
 受注個数     INTEGER ,
 納品日       CHAR(8) ,
 UNIQUE (得意先コード, 納品日)
) ;

DROP TABLE 商品表;
CREATE TABLE 商品表
(
   商品コード  INTEGER PRIMARY KEY ,
   商品名      CHAR(16) UNIQUE NOT NULL ,
   商品単価    INTEGER
) ;

DROP TABLE 受注表;
CREATE TABLE 受注表
(
   受注番号     INTEGER PRIMARY KEY ,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER CHECK(受注個数 >= 10)
) ;

DROP TABLE 受注表;
CREATE TABLE 受注表
 (
   受注番号     INTEGER PRIMARY KEY ,
   得意先コード CHAR(5) ,
   商品コード   CHAR(4) ,
   受注個数     INTEGER ,
   CHECK(受注個数 >= 10 OR 商品コード = '0003')
 ) ; 

DROP TABLE 受注表;
CREATE TABLE 受注表
(
     受注番号     INTEGER PRIMARY KEY ,
     得意先コード CHAR(5) ,
     商品コード   CHAR(4) ,
     受注個数     INTEGER ,
     CONSTRAINT ENTERCHECK
     CHECK(受注個数 >= 10 OR 商品コード = '0003')
) ;

ALTER TABLE 受注表 DROP CONSTRAINT ENTERCHECK ;

DROP TABLE 商品表;
CREATE TABLE 商品表
(
     商品コード  INTEGER NOT NULL PRIMARY KEY ,  
     商品単価    INTEGER DEFAULT 2000
) ;

DROP TABLE 受注表;
CREATE TABLE 受注表
  (
     受注番号      INTEGER PRIMARY KEY ,
     得意先コード  CHAR(5) ,
     商品コード    CHAR(4) ,
     受注個数      INTEGER ,
     納品日        CHAR(8) ,
     FOREIGN KEY (得意先コード) REFERENCES 得意先表(得意先コード) ,
     FOREIGN KEY (商品コード) REFERENCES 商品表(商品コード)
  ) ;  

DROP TABLE 受注表;
CREATE TABLE 受注表
  (
     受注番号      INTEGER PRIMARY KEY ,
     得意先コード  CHAR(5) REFERENCES 得意先表(得意先コード) ,
     商品コード    CHAR(4) REFERENCES 商品表(商品コード) ,
     受注個数      INTEGER ,
     納品日        CHAR(8)
  ) ;  

CREATE TABLE 製品取扱表
  (
     メーカー CHAR(10) ,
     製品     CHAR(10) ,
     PRIMARY KEY (メーカー , 製品)
  ) ;  

CREATE TABLE 販売店取扱表
  (
     販売店   CHAR(10) NOT NULL ,
     メーカー CHAR(10) NOT NULL ,
     製品     CHAR(10) NOT NULL ,
     PRIMARY KEY (販売店 , メーカー , 製品) ,
     FOREIGN KEY (メーカー , 製品) REFERENCES 製品取扱表(メーカー , 製品)
  ) ;