DB2の基礎：制約

レベル：初級

Roman B. Melnyk
(roman_b_melnyk@hotmail.coml)
DB2 Information Development, IBM Canada Ltd.

2004年1月

コンテンツ 「何もなしはお断り!」− NOT NULL制約 「それぞれ1回」− ユニーク制約 「あなた一人だけ！」− 主キー制約 「みんなつながっている」− 外部キー制約 「チェックは怠りなく」− 表チェック制約 まとめ 参考文献 著者について

DB2 UDBでは、制約を使ってデータに関するビジネス・ルールを強制し、データベースの保全性を維持します。この記事では、DB2 UDBでサポートされるさまざまな種類の制約について説明し、制約の種類ごとに例を示します。また、（コマンド行かDB2 コントロールセンターのいずれかを使った）制約の基本的な管理方法についても説明します。

DB2^® Universal Database^TM（DB2 UDB）では、制約を使ってデータに関するビジネス・ルールを強制します。この記事では、以下の種類の制約について説明します。

• NOT NULL
• ユニーク
• 主キー
• 外部キー
• 表チェック

この他に、情報制約と呼ばれる制約の種類があります。上記の5種類の制約と異なり、情報制約はデータベース・マネージャーが強制するものではなく、SQLコンパイラーが照会のパフォーマンスを向上させるために使用します。この記事では、上記の5種類の制約だけを扱い、情報制約については説明しません。

表を新規作成するときは、1つまたは複数のDB2 UDB制約を定義できます。一部の制約については、後で表を変更することによって定義できます。CREATE TABLEステートメントは非常に複雑です。あまりに複雑なため、制約の定義ではごく一部のオプションしか使用しないにもかかわらず、構文図（図1と図2）に表示すると、それらのオプションだけで十分に複雑に見えます。制約の管理には、DB2 コントロールセンターを使用する方が簡単で便利です。

図1.CREATE TABLEステートメントの構文の一部（制約の定義に使用する文節のみ）

制約の定義は、適用先のデータベースに関連付けられ、データベース・カタログに格納されます（表1）。定義情報を取り寄せて調べるには、データベース・カタログを照会します。この操作は、コマンド行から直接実行できます（その前にデータベースとの接続を確立することを忘れずに）が、やはりコントロールセンターを使って定義情報にアクセスする方が便利です。

作成した制約は、他のデータベース・オブジェクトと同じように扱われます。名前が付けられ、対応するスキーマ（クリエーターID）があり、場合によってはドロップ（削除）できます。

図2.CREATE TABLEステートメントの構文の一部
（制約の定義に使用する文節のみ）（続き）

表1.データベース・カタログ内の制約情報
（カタログに対する照会を正常に実行するには、データベースに接続する必要があります。）

「何もなしはお断り!」− NOT NULL制約
NOT NULL制約は、NULL値が列に追加されるのを防止します。これにより、表内のすべての行で必ずその列に意味のある値が格納されるようになります。たとえば、SAMPLEデータベース内のEMPLOYEE表の定義でLASTNAME VARCHAR(15) NOT NULLと指定すると、各行に従業員の姓が必ず格納されるようになります。

列がNULL可能かどうかを確認するには、表のデータ定義言語（DDL）（db2lookユーティリティーを実行して生成する）を参照するか、DB2 コントロールセンター（図3と図4）を使用するか、またはデータベース・カタログ（リスト1）を照会します。

図3.DB2 コントロールセンターのオブジェクト・ツリーで「表」を選択することによって、特定のデータベースに関連付けられた表をコンテンツ・ペインに表示する
（このリストは、melnykスキーマでフィルタリングされています。）

DB2 コントロールセンターを使用すると、表などのデータベース・オブジェクトに簡単にアクセスできます。図3では、SAMPLEデータベース内のユーザー表が表示されています。これらの表は、オブジェクト・ツリーでTables（表）を選択したときにコンテンツ・ペインに表示されます。EMPLOYEE表を選択すると、Alter Table（表の変更）ウィンドウを開いて、列の属性（図4）を含む表の定義を参照することができます。

図4.Alter Table（表の変更）ウィンドウを使って、表の属性を参照する

リスト1.　データベース・カタログを照会してNULL可能な列を確認する

db2 select tabname, colname, nulls
    from syscat.columns
    where tabschema = 'MELNYK' and nulls = 'N'

「それぞれ1回」− ユニーク制約
ユニーク制約は、表内の特定の列に同じ値が複数回現れるのを防止します。また、特定の列の組み合わせの中に同じ値の組み合わせが複数回現れることも防止できます。ユニーク制約で参照される列は、NOT NULLとして定義されていなければなりません。ユニーク制約は、CREATE TABLEステートメントでUNIQUE文節（図1と図2）を使って定義するか、次のようにALTER TABLEステートメントで定義します。

リスト2.ユニーク制約の作成
（ORG_TEMP表は、LOCATION列がNOT NULLで、その表にユニーク制約を定義できる以外はSAMPLEデータベースのORG表と同じです。）

db2 create table org_temp (
    deptnumb smallint not null,
    deptname varchar(14),
    manager smallint,
    division varchar(10),
    location varchar(13) not null)

db2 alter table org_temp add unique (location)

db2 insert into org_temp
    values (10, 'Head Office', 160, 'Corporate', 'New York')

DB20000I  The SQL command completed successfully.

db2 insert into org_temp
    values (15, 'New England', 50, 'Eastern', 'New York')

SQL0803N  One or more values in the INSERT statement, UPDATE statement, or
foreign key update caused by a DELETE statement are not valid because the
primary key, unique constraint or unique index identified by "1" constrains
table "MELNYK.ORG_TEMP" from having duplicate rows for those columns.
SQLSTATE=23505

ユニーク制約を定義すると、重複した値を誤って指定するおそれがなくなり、データの保全性を確保しやすくなります。この例では、組織の支部の所在地としてNew Yorkを指定した2つ目のレコードを挿入しようとしましたが、ユニーク制約によってこの操作が阻止されました。ユニーク制約は、ユニーク索引を使って強制されます。

「あなた一人だけ！」− 主キー制約
主キー制約は、主キーを構成する列または列の組み合わせに含まれるすべての値がユニーク（固有）であることを保証します。主キーは、表内の特定の行を識別するために使われます。主キーは1つの表に1つしか設定できませんが、ユニーク・キーは複数設定できます。主キー制約はユニーク制約の特別な場合で、1次索引を使って強制されます。

主キー制約で参照される列は、NOT NULLとして定義されていなければなりません。主キー制約は、CREATE TABLEステートメントでPRIMARY KEY文節（図1と図2）を使って定義するか、次のようにALTER TABLEステートメントで定義します。

リスト3.　主キー制約の作成
（EMPLOYEE表のEMPNO列はNULL不可能なので、その表に主キー制約を定義できます。）

db2 alter table employee add primary key (empno)

または、DB2 コントロールセンターを使って表に主キー制約を定義することもできます（図5）。

図5.Alter Table（表の変更）ウィンドウを使って、表に主キー制約を定義する
（Available columns（使用可能な列）リストから1つまたは複数の列を選択し、プッシュボタンをクリックして選択した列名をPrimary key columns（主キー列）リストに移動します。選択する列はNULL不可能でなければなりません。）

「みんなつながっている」− 外部キー制約
外部キー制約は、参照制約と呼ばれることもあります。参照保全性は、「すべての外部キーのすべての値が有効であるデータベースの状態」と定義されます。では、外部キーとは何でしょうか。外部キーは、表内の列または列の組み合わせで、その値が親表内の行の主キーまたはユニーク・キーの少なくとも1つの値と一致するものです。これはどういう意味でしょうか。実際にはそれほど込み入った話ではありません。要するに、表（T2）内の列（C2）の値が別の表（T1）の列（C1）の値と一致し、かつ、C1がT1の主キー列であれば、C2はT2の外部キー列になります。親キー（主キーまたはユニーク・キー）を含む表を親表と呼び、外部キーを含む表を従属表と呼びます。具体例を見てみましょう。

SAMPLEデータベースのPROJECT表には、RESPEMPという列があります。この列の値は、表内に含まれる各プロジェクトを担当する従業員の社員番号を表しています。RESPEMPはNULL不可能です。この列はEMPLOYEE表のEMPNO列に対応しており、EMPNOはEMPLOYEE表の主キーであることがすでにわかっているので、RESPEMPをPROJECT表の外部キーとして定義できます（リスト4）。こうしておけば、今後EMPLOYEE表から従業員が削除されても、PROJECT表に「存在しない」担当従業員が残ることはありません。

外部キー制約は、CREATE TABLEステートメントでFOREIGN KEY文節（図1と図2）を使って定義するか、次のようにALTER TABLEステートメントで定義します。

リスト4.外部キー制約の作成

db2 alter table project add foreign key (respemp) references employee on delete cascade

REFERENCES文節には、この参照制約の親表を指定します。外部キー制約を定義する構文には、rule-clauseがあります。ここには、参照保全性の観点から更新操作や削除操作に対してどう対応するかを指定します（図1）。

挿入操作に対しては、制御を一切しない標準的な方法で対応します。参照制約の挿入規則は、「外部キーの挿入値は親表の親キーの値と一致しなければならない」というものです。当然のことであり、すでに述べたこととも一致します。PROJECTテーブルに新しいレコードを挿入する場合、そのレコードにはEMPLOYEE表内の既存レコードへの（親-外部キー関係による）参照が含まれていなければなりません。

参照制約の更新規則は、「外部キーの更新値は親表の親キーの値と一致しなければならない」および「親キーに対する更新操作の完了時は、すべての外部キー値について対応する親キー値がなければならない」というものです。これも、やはり「孤児」を作ってはならない、つまり、すべての従属に親がなければならないという意味です。

参照制約の削除規則は、親表から行を削除するときに適用され、参照制約を定義するときに指定するオプションによって異なります。RESTRICT文節またはNO ACTION文節を指定すると、行は削除されません。SET NULL文節を指定すると、外部キーのNULL可能列がすべてNULLに設定されます。一方、参照制約の作成時にCASCADEオプションを指定すると、削除操作が親表の従属にも適用されます。このような従属を、「親表への連結削除」と言います。

以上の点について、次の例を使って説明します。

リスト5.外部キー制約の更新規則と削除規則の具体例

db2 update employee set empno = '350' where empno = '000190'
DB20000I  The SQL command completed successfully.

db2 update employee set empno = '360' where empno = '000150'
SQL0531N  The parent key in a parent row of relationship
"MELNYK.PROJECT.SQL040103212526610" cannot be updated.  SQLSTATE=23504

db2 "select respemp from project where respemp < '000050' order by respemp"

RESPEMP
-------
000010
000010
000020
000030
000030

db2 delete from employee where empno = '000010'
DB20000I  The SQL command completed successfully.

db2 "select respemp from project where respemp < '000050' order by respemp"

RESPEMP
-------
000020
000030
000030

親表（EMPLOYEE）のEMPNOの値「000190」は、従属表（PROJECT）のRESPEMPの値として「000190」が存在しないので、変更できます。一方、EMPNOの値「000150」は、PROJECT表に一致する外部キー値があるので更新できません。また、削除規則としてCASCADEオプションを指定すると、EMPLOYEE表から主キー値「000010」を削除したときに、連結削除にあるPROJECT表から、一致する外部キー値を持つ行がすべて消失します。

「チェックは怠りなく」− 表チェック制約
表チェック制約は、表に追加されるデータに対して定義された制限事項を強制します。たとえば、EMPLOYEE表で従業員の内線番号を追加または更新する場合、表チェック制約によって番号が必ず4桁になるように保証できます。表チェック制約は、CREATE TABLEステートメントでCHECK文節（図1と図2）を使って定義するか、次のようにALTER TABLEステートメントで定義します。

リスト6.表チェック制約の作成
（PHONENO_LENGTH制約によって、EMPLOYEE表に追加される内線番号が必ず4桁になるように保証します。）

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

または、DB2 コントロールセンターを使って表チェック制約を定義することもできます（図6）。

図6.Alter Table（表の変更）ウィンドウを使って、列に表チェック制約を定義する

新しい制約を定義するには、Add（追加）ボタンをクリックします（Add Check Constraint（チェック制約の追加）ウィンドウが開きます）。リストから選択した既存の制約を変更するには、Change（変更）ボタンをクリックします（図7）。

図7.Change Check Constraint
（チェック制約の変更）ウィンドウで、既存のチェック制約を変更する

新しい表チェック制約に違反する値が表内の既存の行に含まれている場合は、その制約を作成できません（図8）。制約を正常に追加または変更するには、違反する値をあらかじめ適切に更新しておきます

図8.表内の既存の値が新しい表チェック制約に違反する場合は、エラーが返される

表チェック制約は、SET INTEGRITYステートメントを使って有効または無効にできます。これは、表に大量のデータを読み込む操作のパフォーマンスを最適化するときなどに非常に便利です。リスト7は、SET INTEGRITYステートメントの1つの使用方法を示す簡単なシナリオです。この例では、従業員「000100」の内線番号を123に更新した後、EMPLOYEE表の保全性のチェックを無効にしています。次に、EMPLOYEE表に対して内線番号を必ず4桁にするためのチェック制約を定義しています。さらに、EMPL_EXCEPTという例外表を作成しています。この表の定義は、EMPLOYEE表の定義を継承しています。保全性のチェックを有効にすると、チェック制約に違反する行が例外表に書き込まれます。これらの表を照会すると、問題の行が例外表にしか存在しないことが確認できます。

リスト7.SET INTEGRITYステートメントを使って制約のチェックを据え置く

db2 update employee set phoneno = '123' where empno = '000100'

db2 set integrity for employee off

db2 alter table employee add constraint phoneno_length check (length(rtrim(phoneno)) = 4)

db2 create table empl_except like employee

db2 set integrity for employee immediate checked for exception in employee use empl_except
SQL3602W  Check data processing found constraint violations and moved them to
exception tables.  SQLSTATE=01603

db2 select empno, lastname, workdept, phoneno from empl_except

EMPNO  LASTNAME        WORKDEPT PHONENO
------ --------------- -------- -------
000100 SPENSER         E21      123

  1 record(s) selected.

まとめ
この記事では、DB2 Universal Databaseでサポートされるさまざまな種類の制約（NOT NULL制約、ユニーク制約、主キー制約、外部キー（参照）制約、および表チェック制約）について説明しました。DB2 UDBで制約を使ってデータに関するビジネス・ルールを強制し、データベースの保全性を維持する方法を示しました。また、コマンド行とDB2 コントロールセンター（およびデータベース・カタログに対する照会）を使って制約を効率的に管理する方法についても説明しました。

参考文献

• DB2テクニカル・サポートのサイトは、バージョン8インフォメーション・センターやPDF形式の製品マニュアルなどの参考文献を見つけるのに理想的な場所です。

著者について

IBM、DB2およびDB2 Universal Databaseは、米国、その他の国またはその両方におけるIBM Corporationの商標または登録商標です。
その他の会社名、製品およびサービス名は、一般に所有各社の商標です。

IBMの著作権および商標情報

上に戻る