制約: データベースの整合性

データベースの整合性管理

データベースについて知っておくべきもう 1 つの重要な点は、CONSTRAINS です。制約を利用すると、テーブル内のデータ変更を制御し、テーブルの整合性と一貫性を維持できます。

データベースについて話すとき、データの一貫性とは何でしょうか?

従業員、製品、およびタスクのテーブルを備えたオンライン ストアを考えてみましょう。タスク テーブルには誰にも割り当てられていないタスクが存在する可能性があることはすでにわかっています。そのような行の employee_id はNULL です。

しかし、タスク テーブルに、employee_id がたとえば 115 に等しいエントリがある場合はどうなるでしょうか? 結局のところ、当社にはそのような従業員はいません。従業員テーブルには ID = 115 の従業員がいません。同時に、この ID を持つ従業員へのリンクがタスク テーブルにあります。これはデータの不整合の例です。

では、これらのデータをどのように調整すればよいのでしょうか? 理想的には、データに変更があった場合でも、SQL サーバーがこれらすべてのニュアンスを制御できるようにする必要があります。そして、そのような機会があり、それは FOREIGN_KEY と呼ばれます。

テーブル内の一部の列に数値だけでなく、別のテーブルの ID 行が含まれている場合は、これを明示的に指定できます。

外部キーの追加

このようなキーは、テーブルの作成段階でも、ALTER TABLE を使用した後のテーブルにも追加できます。フォーマットは基本的には変わりません。両方のオプションをご紹介します。

このようなキー/ルールの一般的な形式は次のとおりです。

FOREIGN KEY (column)
  	REFERENCES table(column)

このキー/ルールをタスクテーブルに追加して、テーブルのすべてのemployee_id が従業員テーブルの既存のエントリを参照するようにしましょう。このスクリプトは次のようになります。

ALTER TABLE task
      ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)

タスク テーブルの作成時にこのルールを追加することにした場合、コードは次のようになります。

CREATE TABLE task (
      id INT,
      name VARCHAR(100),
      employee_id INT,
      deadline DATE,
 
      PRIMARY KEY (id),
  	  FOREIGN KEY (employee_id)  
	      REFERENCES employee(id)
);

ちなみに、「名前と生年」や「productCatogoryIdとproductId」など、参照する文字列が一意の複合キーを持つ場合もあります。FOREIGN KEY は次のように記述できます。

FOREIGN KEY (our_column1, our_column2)
  	REFERENCES table(their_column1, their_column2)

FOREIGN KEY と変更データ

ここで、従業員テーブルの一部のデータを更新することにし、従業員 ID が変更された状況を想像してください。タスクテーブル内のデータはどうなりますか? そうです、それらは無関係になり、データベースの整合性が侵害されます。

これを防ぐには、従業員テーブルの ID が変更されたときに、この特定の変更された ID を参照するすべてのテーブルのすべての行の従業員 ID を変更するように SQL Server に指示できます。

このようなスクリプトはOnUpdateおよびOnDeleteと呼ばれます。レコード ID が変更された場合はどうすればよいですか?また、レコードが削除された場合はどうすればよいですか?

削除に関しては、すべてがそれほど単純ではありません。データベース内に相互参照する文字列で表される依存オブジェクトがある場合、1 つのオブジェクトを削除するときにさまざまな動作シナリオが考えられます。

サイト ユーザーを削除するとします。これは、そのユーザーの個人的な通信をすべて削除する必要があることを意味します。しかし、彼のパブリックコメントをすべて削除する必要があるとは考えにくい。

もしくは従業員が辞めてしまう。彼が辞めると同時に、彼に割り当てられたすべてのタスクがデータベースから消えてしまったら、奇妙です。しかし、もし彼らが彼によって任命されなかったとしたら、それはまたひどい結果になっただろう。すべての仕事を他の人に割り当て直した後で退職できるようにするのがより正確です。

FOREIGN KEY を使用してこれらのシナリオを記述する方法を次に示します。このようなキー/ルールの一般的な形式は次のとおりです。

FOREIGN KEY (column)
  	REFERENCES table(column)
 	[ON DELETE reference_option]
 	[ON UPDATE reference_option]

レコードを削除 (ON DELETE) または変更 (ON UPDATE) した場合はどうすればよいですか? これらの各状況で SQL サーバーが動作するには、合計で 5 つのオプションがあります。

タスク テーブルを変更する方法は次のとおりです。

ALTER TABLE task
  	ADD FOREIGN KEY (employee_id)
  	REFERENCES employee(id)
  	ON UPDATE CASCADE
  	ON DELETE RESTRICT;

ここに書かれていること:

ON UPDATE CASCADE :employee テーブルの id キーが変更されると、それを参照するタスク テーブルのemployee_id も変更されます。

ON DELETE RESTRICT : 行が従業員テーブルから削除されており、タスクテーブルから参照されている場合、その行が従業員テーブルから削除されないようにします。