お問い合わせ
IBSurgeon
このページは機械翻訳されています。英語の原文をお読みください。 English

IBSurgeon ライブラリ

Firebird と InterBase におけるデータベース制約

NOTICE: This document is the chapter from the book “The InterBase World” which was written by Alexey Kovyazin and Serg Vostrikov.

この章は、InterBase および Firebird データベースの制約に充てられています。データベース制約とは、テーブル間の相互関係を定義し、データベース内のデータをチェックおよび変更できるルールです。これらのルールは、特別なデータベースオブジェクトとして実現されます。制約を使用する主な利点は、データチェックとアプリケーションのビジネスロジックの一部をデータベースレベルで実装できること、つまり集中化および簡素化できることであり、これによりデータベースアプリケーションの開発がより容易で信頼性の高いものになります。

初心者の開発者は、制約が創造的な作業を妨げると考えて、データベース制約の使用を軽視することがよくあります。しかし、実際にはそのような意見は、データベース設計の理論と実践に関する知識不足から形成されています。

一方で、最も経験豊富な設計者は、一部の種類の制約の使用をあえて拒否することがあり、その結果、アプリケーションは速度で勝ります。専門家の設計者の経験により、サーバーの動作を非常によく理解し、複雑なケースでのその動作を正確に予測できるため、InterBase の初心者プログラマーは経験豊富な同僚の同様の行動に訴えない方がよいでしょう。

この本ではデータベース設計については扱いません。そのため、この問題の詳細については、本書の最後にある文献リストを参照してください。ここでは、InterBase データベースのすべての種類の制約をレビューし、その適用例を検討します。

データベースの制約の種類

InterBase データベースには、次の種類の制約があります：

  • PRIMARY KEY;
  • UNIQUE KEY;
  • FOREIGN KEY

- 自動トリガーをオンにできる - ON UPDATE および ON DELETE;

  • CHECK

前の章では、資料の論理的な提示のためにこれらの制約の一部に言及しましたが、ここではその構文、適用、および実装についてより詳細に検討します。データベース制約には、1つのフィールドに基づくものと、テーブルの複数のフィールドに基づくものの2種類があります。両方の種類の制約の構文を以下に示します。

= [CONSTRAINT constraint]

[ …]

= {UNIQUE | PRIMARY KEY

| CHECK ( )

| REFERENCES other_table [( other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

}

複数のフィールドに基づく制約の構文は次のとおりです：

= [CONSTRAINT constraint]

[< tconstraint> …]

= {{PRIMARY KEY | UNIQUE} ( col [, col …])

| FOREIGN KEY ( col [, col …]) REFERENCES other_table[( other_col [, other_col …])]

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

[ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

| CHECK ( )}

1つのフィールドに基づく制約と複数のフィールドに基づく制約の構文の違いは明らかです - 後者では、制約に含まれる複数のフィールドを指定できます。1つのフィールドに基づく制約の場合、説明されているすべてのオプションは現在のフィールドにのみ関連します。確かに、これら2種類の制約には適用方法が異なります：1つのフィールドに基づく制約は、必要なフィールドの定義に単純に追加され、複数のフィールドに基づく制約は、テーブルの一般的な定義でカンマの後に指定されます。詳細な例は、この章の以下の部分で示されています。

典型的な制約の例

実際には、1つのフィールドに基づく制約は、複数のフィールドに基づく制約の特別なケースです。

これら2つの異なるアプローチを使用して主キー制約を作成する例を以下に示します。1つのフィールドのみを含むテーブルを作成し、それに主キー制約を設定しましょう。

1つのフィールドに基づく制約の構文を使用した主キーの例を次に示します：

CREATE TABLE test1( ID_PK INTEGER CONSTRAINT pktest NOT NULL PRIMARY KEY); この例では、ID_PK フィールドに対して pktest という名前の主キーが作成されます。その結果、1行でかなりコンパクトな記述が得られます。同じ目的で複数のフィールドに基づく制約の構文を使用できます： CREATE TABLE test2( ID_PK INTEGER NOT NULL, CONSTRAINT pktst PRIMARY KEY (ID_PK));

制約の作成

制約の作成をより詳細に検討しましょう。一般的な制約構文の説明の最初に [CONSTRAINT constraint] オプションがあります。ご覧のとおり、このオプションは角括弧で囲まれており、つまりオプションです。

このオプションを使用すると、1つのフィールドに基づく制約の構文を適用する場合も、複数のフィールドに基づく制約の場合も、作成された制約に名前を設定できます。制約の名前を指定していない場合、InterBase は自動的に生成します。それでも、データベーススキーマの可読性を向上させ、後での制約の管理を簡素化するために、作成された制約に名前を設定する方がよいでしょう。

制約に名前を設定したら、そのタイプを定義する必要があります。一般的な制約構文の説明で示されている順序で、さまざまな種類の制約を検討しましょう。

主キーとユニークキー

主キーは、データベース制約の主要な種類の1つです。これらは、テーブル内のレコードの一意の識別に適用されます。データベースに人物のリストを保存すると仮定しましょう。同じ姓、名、父称を持つ2人（またはそれ以上）の人物が存在する可能性は十分にあります。ある人物を別の人物からどのように区別できるでしょうか（もちろん、問題はデータベースに保存されている情報に従ってある人物を別の人物から区別することです）？

この場合、「人物」はテーブル内の1つのレコードで表されるため、より一般的な質問をすることができます - （任意の）テーブル内の1つのレコードを同じテーブル内の別のレコードからどのように区別できるでしょうか。この目的のために、制約 - 主キーが使用されます。主キーは、テーブル内の1つまたはいくつかのフィールドを表し、その組み合わせはすべてのレコードに対して一意です。1つのテーブルに対して主キーの繰り返し値はありません。

ユニークキーも同じ機能を実行します - テーブル内のレコードの一意の識別にも役立ちます。主キーとユニークキーの違いは、テーブルには主キーを1つだけ持つことができ、ユニークキーは複数持つことができることです。主キーとユニークキーの両方が外部キーの参照基準として使用できることに注意してください（後述参照）。

主キーとユニークキーの概念の正式な説明、およびその他の重要な定義は、本書の最後にある付録「用語集」にあります。一意のフィールドに基づく主キーとユニークキーを作成する構文は次のとおりです：

< pkukconstraint > = [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE}

主キーとユニークキーの例：

CREATE TABLE pkuk( pk NUMERIC(15,0) NOT NULL PRIMARY KEY, /*主キー*/

uk1 VARCHAR(50) NOT NULL UNIQUE,/*ユニークキー*/

uk2 INTEGER NOT NULL UNIQUE /\* もう1つのユニークキー */);

複数のフィールドに基づく主キーとユニークキーを作成する構文：

= [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

このような構文により、フィールドの組み合わせに基づいてキーを作成できます。複数のフィールドから主キーとユニークキーを作成する例を次に示します：

CREATE TABLE pkuk2( Number1 INTEGER NOT NULL, Name1 VARCHAR(50) NOT NULL, Kol INTEGER NOT NULL, Stoim NUMERIC(15,4) NOT NULL, CONSTRAINT pkt PRIMARY KEY (Number1, Name1), /*2つのフィールドに基づく主キー pkt*/ CONSTRAINT ukt1 UNIQUE (kol, Stoim)); /*2つのフィールドに基づくユニークキー ukt1*/

主キーとユニークキーに含まれるすべてのフィールドは、これらのキーは未定義の値を持つことができないため、NOT NULL として宣言する必要があることに注意してください。テーブルの作成時に主キーとユニークキーの制約を作成するだけでなく、既存のテーブルに制約を追加する機能もあります。この場合、DDL: ALTER TABLE ステートメントが使用されます。既存のテーブルに主キーまたはユニークキーの制約を追加する構文は、上記で説明したものと類似しています：

ALTER TABLE tablename ADD [CONSTRAINT constraint] {PRIMARY KEY | UNIQUE} ( col [, col …])

ALTER TABLEを使用して主キーとユニークキーを作成する例を考えてみましょう：

CREATE TABLE pkalter( ID1 INTEGER NOT NULL, ID2 INTEGER NOT NULL, UID VARCHAR(24));

次にキーを追加します。まず主キー：

ALTER TABLE pkalter ADD CONSTRAINT pkal1 PRIMARY KEY (id1, id2);

次にユニークキー： ALTER TABLE pkalter ADD CONSTRAINT ukal UNIQUE (uid);

このテーブルの所有者またはシステム管理者SYSDBAのみが（詳細については、所有者とSYSDBAユーザーについては、「InterBaseのセキュリティ：ユーザー、その機能と権利」の章 - パート4を参照）、テーブルへの主キーとユニークキーの追加（および削除）を実行できることに注意してください。

外部キー

InterBaseデータベースで頻繁に使用される次の制約は、外部キー制約です。これは、データベースに参照整合性を提供するための非常に強力なツールであり、データベース内の正しい参照の存在を監視するだけでなく、これらの参照を自動的に制御することもできます！

外部キーを作成するポイントは次のとおりです：2つのテーブルが相互に関連する情報を格納するために使用される場合、この相互関係が常に正しいことを保証する必要があります。例えば、文書「納品書」には、一般的な見出し（日付、納品書番号など）と一連の詳細レコード（商品の説明、数量など）が含まれています。

このような文書を格納するために、データベースに2つのテーブルが作成されます - 1つは納品書の見出しを格納するためのもの、2つ目は納品書の内容 - 商品とその数量に関するレコードを格納するためのものです。このようなテーブルは、メインテーブルとサブテーブル、またはマスターテーブルと詳細テーブルと呼ばれます。

常識的に考えて、納品書の内容は、その見出しが存在しなければ存在できません。言い換えれば、納品書の見出しを作成していなければ商品に関するレコードを挿入することはできず、商品に関するレコードが存在する場合には見出しのレコードを削除することはできません。このような動作を実現するために、見出しテーブルと詳細テーブルは外部キー制約を使用して結合されます。

納品書に関する情報を含むテーブルの例を使用して、外部キー制約を設定する意味を考えてみましょう。この目的のために、納品書を格納するための2つのテーブルを作成します - 見出しを格納するためのTITLEテーブルと、納品書に含まれる商品に関する情報を格納するためのINVENTORYテーブルです。

CREATE TABLE TITLE( ID_TITLE INTEGER NOT NULL Primary Key, DateNakl DATE, NumNakl INTEGER, NoteNakl VARCHAR(255));

見出しテーブルでID_TITLEフィールドに基づいて主キーをすぐに定義したことに注意してください。TITLEテーブルの残りのフィールドには、納品書の見出しに関する簡単な情報 - 日付、番号、コメントが含まれています。

次に、納品書に含まれる商品に関する情報を格納するためのテーブルを定義しましょう：

CREATE TABLE INVENTORY( ID_INVENTORY INTEGER NOT NULL PRIMARY KEY, FK_TITLE INTEGER NOT NULL, ProductName VARCHAR (255), Kolvo DOUBLE PRECISION, Positio INTEGER);

INVENTORYテーブルに含まれるフィールドを見てみましょう。まず、ID_INVENTORY - このテーブルの主キーです。次に、納品書見出しテーブル内のID_TITLE見出しの識別子への参照として機能する整数フィールドFK_TITLEがあります。続いて、商品の説明、その数量、納品書内の位置を記述するProductName、Kolvo、Positioフィールドがあります。FK_TITLEフィールドは、この例で最も重要です。特定の納品書の商品に関する情報を出力したい場合は、mas_ID_TITLEパラメータが見出し識別子を定義する次のクエリを使用する必要があります：

SELECT * FROM INVENTORY I1 WHERE I1.FK_TITLE=?mas_ID_TITLE

実際には、説明した状況では、TITLEテーブルに存在しないレコードを参照するレコードをINVENTORYテーブルに入力することを妨げるものは何もありません。さらに、既存の納品書の見出しを削除することを妨げるものもなく、その結果、商品に関するレコードが「所有者なし」になる可能性があります。サーバーはこれらすべての挿入と削除の実行を禁止しません。したがって、データベース内のデータ整合性の制御は完全にクライアントアプリケーションに委ねられています。ただし、おそらく異なるプログラマーによって開発された複数のアプリケーションが1つのデータベースで動作できることをご存知でしょう。これは異なるデータ解釈やエラーにつながる可能性があります。その結果、納品書の見出しへの正しい参照を持つ商品に関するレコードのみをINVENTORYテーブルに配置できるという明示的な制約を設定することが不可欠です。これは実際には外部キー制約であり、制約に含まれるフィールドに、他のテーブルにある値のみを挿入できるようにします。

このような制約は、外部キーを使用して作成できます。この例では、FK_TITLEフィールドに外部キー制約を設定し、TITLE内のID_TITLE主キーにバインドする必要があります。既存のテーブルに外部キーを追加するには、次のコマンドを使用できます：

ALTER TABLE INVENTORY ADD CONSTRAINT fktitle1 FOREIGN KEY(FK_TITLE) REFERENCES TITLE(ID_TITLE)

外部キーを追加するとき、エラー - object is in use（オブジェクトは使用中です）が頻繁に発生します。問題は、外部キーを作成するには、データベースをバーストモードで開く必要があることです - 同時に他のユーザーがいないようにする必要があります。また、変更するテーブルを参照しないでください - object is in use（オブジェクトは使用中です）が発生する可能性があります。

ここで、INVENTORYは外部キー制約が設定されるテーブルの名前です。fktitle1は外部キーの名前です。FK_TITLE - 外部キーを構成するフィールドです。TITLEは外部キーに値（参照基準）を提供するテーブルの名前です。ID_TITLE - 外部キーの参照基準として機能するTITLEテーブルの主キーまたはユニークキーのフィールドです。外部キー制約の完全な構文（複数のフィールドに基づく制約を作成する可能性を含む）は以下のとおりです：

= [CONSTRAINT constraint] FOREIGN KEY ( col [, col …]) REFERENCES other_table [( other_col [, other_col …])] [ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

ご覧のとおり、定義には多くのオプションが含まれています。まず、実際のデータベースで最も頻繁に使用される外部キーの基本定義を検討し、次に可能なオプションを分析しましょう。

外部キー制約の宣言形式は、制約を構成するフィールドのセット（col [, col …]）が指定され、other_tableがフィールド[(other_col [, other_col …])]に外部キーの可能な値のリストを含む場合に最も頻繁に使用されます。

テーブル作成時のこのような定義の例を次に示します：

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL CONSTRAINT fkinv REFERENCES TITLE(ID_TITLE) …);

この定義では、FOREIGN KEYキーワードが省略されていることに注意してください。また、唯一のフィールドFK_TITLEが外部キーとして使用されることが暗黙的に示されています。テーブルと同時に外部キーを作成するより完全な形式は、次の例に示されています：

CREATE TABLE Inventory2( … FK_TABLE INTEGER NOT NULL, CONSTRAINT fkinv FOREIGN KEY (FK_TABLE) REFERENCES TITLE(ID_TITLE) …);

外部キーのフィールドでのNULLの使用

外部キーが作成される基になるフィールドでは、NULLフィールドの適用が許可されています。この可能性は、相互参照を許可するために追加されました。例えば、外部キーを使用して相互に参照する2つのテーブルがある場合です。これらの外部キーで空の参照（つまりNULL）を許可しない場合、結合されたテーブルにレコードを追加することは不可能になります：最初のテーブルにレコードを追加するには、2番目のテーブルにレコードが必要であり、その逆も同様です。

空の参照としてNULLを使用すると、相互に参照する2つのテーブルの相互参照を作成でき、またリレーショナルテーブルに階層構造を格納することもできます - その場合、ルートノードは「空の」レコード（つまり単にNULLを含む）を参照します。

使用外键扩展引用完整性支持的功能

通常，外键约束的声明式变体已经足够，服务器只会监视确保不可能向带有外键的表中插入不正确的值–或者，如果尝试这样做，会出现错误。但InterBase允许在更改/删除外键时执行一组自动操作。为此，使用以下一组外键选项：

[ON DELETE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}] [ON UPDATE {NO ACTION|CASCADE|SET DEFAULT|SET NULL}]

这些选项允许在更新或删除外键值时定义不同的操作。

例如，我们可以设置当删除主表中的主键时，从表中所有具有相同外键的记录也应被删除。在这种情况下，我们必须按以下方式定义外键：

ALTER TABLE INVENTORY ADD CONSTRAINT fkautodel FOREIGN KEY (FK_TITLE) REFERENCES TITLE(ID_TITLE) ON DELETE CASCADE

实际上，为了实现这些操作，有一个系统触发器会执行某些操作。在表1.2中描述了不同选项的操作（请注意，NO ACTION|CASCADE|SET DEFAULT|SET NULL选项不能在同一句ON XXX中一起使用）。

表1.2
事件 操作
NO ACTION CASCADE SET DEFAULT SET NULL
ON DELETE 删除外键时不执行任何操作–这是默认行为 删除时从从表中删除所有相关记录 更改时
将外键字段设置为默认值		 更改时
将外键字段设置为NULL
ON UPDATE 更改时不执行任何操作–这是默认行为 更改记录时更改从表中的所有相关记录 删除时
将外键字段设置为默认值		 删除时
将外键字段设置为NULL

如果我们不指定任何内容或指定NO ACTION，则需自行处理外键的更改（在更改主键的情况下），并且在删除主键时应事先删除从表中的记录。使用CASCADE选项时要非常小心：粗心使用可能导致删除大量相关记录。

CHECK约束

数据库中一个最有用的约束是检查约束。其功能非常简单–检查插入表中的值是否满足某个条件，并根据该条件的执行结果来决定插入数据还是拒绝。其语法相当简单：

= [CONSTRAINT constraint] CHECK ( )}

其中constraint是约束的名称；是搜索条件，插入/更新的值可以作为参数。如果搜索条件满足，则允许插入/更新该值；如果不满足，则出现错误。最简单的检查示例：

create table checktst( ID integer CHECK(ID>0));

此检查确定插入/更新的ID字段值是否大于零，并根据结果允许插入/更新新值或报告错误（参见“InterBase存储过程语言的扩展功能”一章（第1部分））。

还有更复杂的检查变体。搜索条件的完整语法如下：

= {

{ | ()}

| [NOT] BETWEEN AND

| [NOT] LIKE [ESCAPE ]

| [NOT] IN ( [ , …] | )

| IS [NOT] NULL

| {[NOT] {= | < | >} | >= | <=}

{ALL | SOME | ANY} ()

| EXISTS ( )

| SINGULAR ( )

| [NOT] CONTAINING

| [NOT] STARTING [WITH]

| ()

| NOT

| OR

| AND }

因此，CHECK为检查插入/更新的值提供了大量选项。使用CHECK时，应记住以下限制：

  • CHECK的数据仅取自当前记录。不应从同一表的其他记录中获取CHECK表达式中的数据–这些记录可能被其他用户更改
  • 一个字段只能有一个CHECK约束
  • 如果字段定义使用了具有CHECK域约束的域，则不能在表中具体字段级别重新定义它。必须说明，CHECK是通过系统触发器实现的，因此在使用非常长的条件时要更加谨慎，这些条件可能会严重减慢记录的插入和更新过程。

删除约束

很多时候，我们会出于各种不同的原因删除各种约束。为了删除约束，应使用以下形式的ALTER TABLE语句：ALTER TABLE tablename DROP CONSTRAINT constraintname

constraintname是要删除的约束的名称。如果在创建约束时指定了某个名称，则应使用该名称；但如果没有指定，则必须打开任何InterBase管理工具，搜索与其相关的所有约束，并找出InterBase为所需约束生成的系统名称。

应注意，只有表的所有者或SYSDBA系统管理员才能删除约束。