3.4. データ値の制約

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

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

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

テーブル制約は 3.2. 節で説明した ALTER TABLE 文を使用することで、作成済のテーブルにも追加することができます。

3.4.1. 主キーの指定 <PRIMARY KEY>

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

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

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

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

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

3.4.2. 一意性制約 <UNIQUE>

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

• PRIMARY KEY 制約は、あるテーブルの 1つの列または複数の列に1度だけ使用できるが、UNIQUE 制約は何度でも使用できる。
• PRIMARY KEY 制約を持つ列には NULL が含まれないが、UNIQUE 制約を持つ列には NULL が含まれる可能性がある。

商品表の商品コードに主キーを、商品名に UNIQUE 制約を指定する場合、SQL では sample.04-1 のように書きます。

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

このように、UNIQUE 列は PRIMARY 列と同じテーブルに宣言することができます。

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

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

3.4.3. NULL を締め出す制約 <NOT NULL>

CREATE TABLE 文では、NOT NULL 制約を使用することで、列に NULL が許可されるのを防ぐことができます。この制約は列に対してのみ使用できます。

次の商品表の商品名に NOT NULL 制約を指定する場合は、SQL では sample.05 のように書きます。

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

3.4.4. 列値の確認<CHECK>

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

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

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

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

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

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

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

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

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

 /* 制約の名前を削除 */

  ALTER TABLE 受注表 DROP CONSTRAINT ENTERCHECK ;

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

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

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

 /* 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 はエラーとして拒否される。

（実習課題１）

表 3-1 に示す列を持ったテーブル employee を定義する SQL 文を書きなさい。それぞれの列のデータ型等の情報は表 3-2 に、制約は表 3-3 に示すとおりである。

解答例はこちら