実務では、特定の規則をどんな場合にも適用する必要が生じます。 たとえば、あるプロジェクトを担当する従業員は、 給与支払い名簿に載っていなければなりません。 あるいは、特定の処置を自動的に実行する必要があるかもしれません。 たとえば、営業担当者が 1 件契約するごとに、 その歩合給に所定の額を加算する必要があります。
DB2 ユニバーサル・データベースには、この目的のために役立つ手段が用意されています。
最初の部分では、キーに関する重要な概念について説明します。 その後、参照保全、制約、 およびトリガーについて例や図を使って説明します。
キー は、 特定の行を識別したりアクセスしたりするのに使うことのできる列の集まりです。
複数の列で構成されるキーのことを複合キー といいます。 複合キーの指定されている表では、 その複合キー内の列の順序は、 その表内での列の順序に対応するとは限りません。
固有キー は、 値がすべて違っている列 (または列集合) です。 固有キーの列の内容を NULL 値にすることはできません。 この制約は、INSERT および UPDATE ステートメントの実行中にデータベース・マネージャーによって適用されます。 1 つの表に対して、複数の固有キーが可能です。 固有キーはオプションであり、CREATE TABLE または ALTER TABLE ステートメントの中で定義できます。
基本キー は、 表の定義の一部を構成している固有キーです。 1 つの表に対して基本キーは 1 つしか定義できず、 基本キーを構成する列の内容を NULL 値にすることはできません。 基本キーはオプションであり、CREATE TABLE または ALTER TABLE ステートメントの中で定義できます。
外部キー は、 参照制約の定義の中で指定します。 1 つの表に対して、0 個以上の外部キーが可能です。 複合外部キーの値のどの成分も NULL である場合、 その外部キーの値は NULL です。 外部キーはオプションであり、CREATE TABLE ステートメントまたは ALTER TABLE ステートメントの中で定義できます。
固有制約は、1 つの表の中でキーの値が固有であることを保証します。 固有制約はオプションであり、CREATE TABLE または ALTER TABLE ステートメントの中で PRIMARY KEY または UNIQUE 文節を指定することによって定義します。 たとえば、ある表の従業員番号の列に固有制約を定義することによって、 どの従業員の番号も固有の番号になるようにすることができます。
固有制約と外部キーを定義することによって、 表と表の間の関係を定義し、 それによって特定の業務規則を適用させることができます。 固有キーと外部キーの制約の組み合わせは、 一般に「参照保全制約」と呼ばれています。 外部キーによって参照される固有制約は、 親キー と呼ばれます。 外部キーは、特定の親キーを参照します (または特定の親キーに関連しています)。 たとえば、どの従業員 (EMPLOYEE 表) も既存の部署 (DEPARTMENT 表) に属していなければならない、 という規則があるとします。 この場合、EMPLOYEE 表の部署番号を外部キーとして定義し、DEPARTMENT 表の部署番号を基本キーとして定義します。 参照保全制約について、下記の図に示します。
図 4. 外部制約と基本制約により関係を定義しデータを保護する
![]() |
表検査制約 は、 表の各行ごとに評価される条件を指定します。 検査制約は、個々の列ごとに指定できます。 検査制約は、CREATE または ALTER TABLE ステートメントを使って追加します。
この後に示すステートメントでは、次の制約を含む表が作成されます。
CREATE TABLE EMP (ID SMALLINT NOT NULL, NAME VARCHAR(9), DEPT SMALLINT CHECK (DEPT BETWEEN 10 AND 100), JOB CHAR(5) CHECK (JOB IN ('Sales', 'Mgr', 'Clerk')), HIREDATE DATE, SALARY DECIMAL(7,2), COMM DECIMAL(7,2), PRIMARY KEY (ID), CONSTRAINT YEARSAL CHECK (YEAR(HIREDATE) >= 1986 OR SALARY > 40500) )
制約違反になるのは、条件の評価結果が偽になる場合だけです。 たとえば、DEPT が NULL の行を挿入する場合、DEPT の値が 10〜100 の範囲でなければならないことが制約の中で定義されているとしても、 その挿入操作はエラーなしで実行されます。
次のステートメントでは、COMP という名前の制約を EMPLOYEE 表に追加しています。 この制約は、従業員の総所得は $15, 000 より大きい値でなければならない、というものです。
ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000)
表に既存の行が新しい制約に違反していないかどうか検査されます。 この検査は、下記のように SET CONSTRAINTS ステートメントを使うことによって据え置くことができます。
SET CONSTRAINTS FOR EMP OFF ALTER TABLE EMP ADD CONSTRAINT COMP CHECK (SALARY + COMM > 15000) SET CONSTRAINTS FOR EMP IMMEDIATE CHECKED
まず、SET CONSTRAINTS ステートメントを使って、 表に対する制約検査を据え置きます。 その後、実際に制約を検査することなく、1 つまたは複数の制約を表に追加できます。 その後、もう一度 SET CONSTRAINTS ステートメントを使って、 制約検査をオンにすることにより、据え置かれた制約検査を実行します。
トリガー は、 一連のアクションを定義します。 トリガーを活動化するには、 指定の基本表のデータを修正する操作を使います。
トリガーには、以下のような用途があります。
トリガーを使うことは、アプリケーション開発の効率を高めたり、 業務規則を広い範囲に適用したり、 アプリケーションとデータの早期メンテナンスを実現したりすることにつながります。
DB2 ユニバーサル・データベースは、いくつかのタイプのトリガーをサポートしています。 トリガーは、DELETE、INSERT、または UPDATE 操作の前または後に活動化されるように定義できます。 各トリガーには、 トリガー・アクション と呼ばれる一連の SQL ステートメントが含まれます。 それには、検索条件も含めることができます。
後トリガー の場合は、 トリガー・アクションを各行ごとに 1 回実行するか、 ステートメント全体で 1 回実行するかをさらに定義できます。 前トリガー の場合は、 必ず各行ごとにトリガー・アクションを実行します。
トリガー操作実行の前に特定の条件を検査する場合、 または入力値を表に格納する前にその値を変更する場合は、 INSERT、UPDATE、または DELETE ステートメントの前にトリガーを使います。
必要に応じて値を波及させたり、 トリガー操作の一部として必要となるその他の作業 (メッセージ送信など) を実行したりするには、 後トリガーを使います。
次の例では、 前トリガーと後トリガーの使用例を示します。 株価の変動を記録追跡するアプリケーションのことを考えてみましょう。 データベースには、CURRENTQUOTE と QUOTEHISTORY という 2 つの表が含まれており、 それぞれ次のように定義されています。
CREATE TABLE CURRENTQUOTE (SYMBOL VARCHAR(10), QUOTE DECIMAL(5,2), STATUS VARCHAR(9)) CREATE TABLE QUOTEHISTORY (SYMBOL VARCHAR(10), QUOTE DECIMAL(5,2), TIMESTAMP TIMESTAMP)
CURRENTQUOTE の QUOTE 列が次のようなステートメントによって更新されたなら、
UPDATE CURRENTQUOTE SET QUOTE = 68.5 WHERE SYMBOL = 'IBM'
CURRENTQUOTE の STATUS 列を、 下記のような株価の変動の状況を反映するように更新する必要があります。
これは、次の前トリガーによって実現できます。
(1)
CREATE TRIGGER STOCK_STATUS NO CASCADE BEFORE UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE OLD AS OLDQUOTE FOR EACH ROW MODE DB2SQL
(2)
SET NEWQUOTE.STATUS =
(3)
CASE
(4)
WHEN NEWQUOTE.QUOTE >= (SELECT MAX(QUOTE) FROM QUOTEHISTORY WHERE SYMBOL = NEWQUOTE.SYMBOL AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) ) THEN 'High'
(5)
WHEN NEWQUOTE.QUOTE <= (SELECT MIN(QUOTE) FROM QUOTEHISTORY WHERE SYMBOL = NEWQUOTE.SYMBOL AND YEAR(TIMESTAMP) = YEAR(CURRENT DATE) ) THEN 'Low'
(6)
WHEN NEWQUOTE.QUOTE > OLDQUOTE.QUOTE THEN 'Rising' WHEN NEWQUOTE.QUOTE < OLDQUOTE.QUOTE THEN 'Dropping' WHEN NEWQUOTE.QUOTE = OLDQUOTE.QUOTE THEN 'Steady' END
CURRENTQUOTE 表の中の項目を更新するだけでなく、 新しい相場とそのタイム・スタンプを QUOTEHISTORY 表にコピーすることによって監査記録を作成する必要があります。 これは、次の後トリガーによって実現できます。
(1)
CREATE TRIGGER RECORD_HISTORY AFTER UPDATE OF QUOTE ON CURRENTQUOTE REFERENCING NEW AS NEWQUOTE FOR EACH ROW MODE DB2SQL BEGIN ATOMIC
(2)
INSERT INTO QUOTEHISTORY VALUES (NEWQUOTE.SYMBOL, NEWQUOTE.QUOTE, CURRENT TIMESTAMP); END
CURRENT TIMESTAMP は、タイム・スタンプを内容とする特殊レジスターです。 特殊レジスターのリストとその説明については、 特殊レジスターを参照してください。