データベースの削除から逃走までの MySQL の徹底分析_上級編 (I) - データ整合性

1. データ整合性の概要

データの冗長性とは、データベース内に重複したデータが存在することを指し、データの整合性とは、データベース内のデータが実際の状況を正しく反映できることを意味します。

データ整合性とは、データの信頼性と正確性を指します。データ整合性には 4 つのタイプがあります。

A. エンティティ整合性: エンティティ整合性は、テーブルの識別子列または主キーの整合性を強制します (一意制約、主キー制約、または ID 列属性を通じて)。

B. ドメイン整合性: 型 (データ型)、形式 (チェック制約とルールを通じて)、および可能な値の範囲 (外部キー制約、チェック制約、デフォルト値の定義、非 NULL 制約とルールを通じて) を制限します。

C. 参照整合性: 参照整合性は、レコードの削除および入力時にテーブル間の定義された関係を維持します。参照整合性により、キー値がすべてのテーブル間で一貫しており、存在しない値を参照できないことが保証されます。鍵なら。

D. 定義の整合性: トリガーを使用してカスタムビジネスルールを実装するなど、ユーザー自身が定義したビジネスルール。

2. データ整合性の実装方法

MySQL はチェック制約をサポートしていません。列にチェック制約を追加することはできますが、機能しません。

2. エンティティ整合性の実装

1. エンティティ整合性の実装の概要

エンティティ整合性を実装する方法は 2 つあります。

A. 主キー制約: テーブルには主キーを持つ列が 1 つだけあります。値は一意である必要があり、空にすることはできません。innoDB ストレージエンジンの場合、主キーはインデックスです。

B. 一意の値制約: テーブルには、一意の値制約が追加された複数の列を含めることができ、レコードには常に null 値を含めることができます。

エンティティの整合性は主キーと一意の制約によって実現され、テーブル内のレコードに一意の識別子が確実に付与されます。主キーは、主キーと AUTO_INCREMENT PRIMARY KEY の 2 種類に分かれています。

2. 主キー

MySQL の主キーの名前は常に PRIMARY です。主キー制約を作成するときに、テーブルのストレージエンジンが innoDB の場合、システムはデフォルトで列と列の組み合わせに対応する一意のインデックスを作成します。

主キー制約は、一意制約と非ヌル制約の組み合わせに相当します。主キー制約の列は繰り返しが許可されず、ヌル値の出現は許可されません。複数の列の主キー制約の場合、どの列もヌル値を持つことは許可されず、結合された値は繰り返しが許可されません。各テーブルには最大 1 つの主キーを設定できます。主キー制約は、列レベルまたはテーブルレベルで作成できます。

A. テーブルを作成するときに主キーを指定する

テーブルを作成するときに主キーを指定する方法 1:

テーブル製品を作成する
 （
 productID int 主キー、
 pName VARCHAR(10)、
 価格 2倍
 )ENGINE=MyISAM デフォルト CHARSET=utf8;

テーブルを作成するときに主キーを指定する方法 2:

テーブル製品を作成する
 （
 製品ID int、
 pName VARCHAR(10)、
 価格 DOUBLE、制約 pk_s_productID 主キー(productID)
 )ENGINE=MyISAM デフォルト CHARSET=utf8;

指定された主キーを持つテーブルにレコードを挿入する場合、重複する ID は許可されません。主キーの値が指定されていない場合、デフォルトは 0 です。

MylSAM ストレージエンジンは主キー列にインデックスを作成せず、テーブル内のレコードの格納順序は挿入順序と同じです。

InnoDB ストレージエンジンは主キー列にインデックスを自動的に作成し、挿入されたレコードは主キー値の順序で配置されます。

テーブル製品を変更します。ENGINE=InnoDB;

B. 主キーを追加する

テーブルTStudentを変更し、主キー(studentid)を追加します。

C. 主キーを削除する

テーブル TStudent を変更し、主キーを削除します。

3. 主キーの自動増分

AUTO_INCREMENT 主キー

主キー値を指定しない場合は、既存の主キー値の最大値が自動的に 1 増加され、新しいレコードの主キーとして使用されます。主キー値はデフォルトで 1 から始まります。データ型が整数である列に自動インクリメント主キーを追加できます。

A. テーブル作成時に自動増分列を指定する

テーブル製品を作成する
 （
 productID int 主キー AUTO_INCREMENT NULL以外、
 pName VARCHAR(10)、
 価格 2倍
 )ENGINE=MyISAM デフォルト CHARSET=utf8;

B. 既存のテーブルに自動増分列を指定する

テーブル TStudent を変更し、列 studentID int PRIMARY KEY AUTO_INCREMENT を変更します。

C. テーブル内の自動増分列を削除する

テーブル TStudent を変更し、列 studentID int を NULL 以外に変更します。

自動増分列を削除します。自動増分機能はなくなりますが、主キーはそのまま残ります。

4. 複合主キー

テーブルの 2 つ以上の列を使用して主キーを作成します。

A. テーブルを作成するときに複合主キーを指定する

学生テーブルを作成する
 （
 学生ID int、id INT、
 sname VARCHAR(10)、
 スコア int、
 主キー(学生ID、ID)
 )ENGINE=MyISAM デフォルト CHARSET=utf8;

B. テーブルに複合主キーを追加する

学生テーブルを変更し、主キー(学生ID、ID)を追加します。

C. 複合主キーを削除する

テーブル学生を変更し、主キーを削除します。

5. ユニーク制約

一意の制約である UNIQUE KEY は、特定の列または列の組み合わせのデータが繰り返されないことを指定します。

A. テーブルを作成するときに一意制約を指定する

テーブルスコアを作成する
 (sname VARCHAR(10) UNIQUE,
 スコア int が NULL でない
 );

B. 既存の列に一意制約を追加する

テーブルスコアを変更し、CONSTRAINT us_sname UNIQUE(sname) を追加します。

テーブル内の既存のレコードに重複する値がある場合、一意制約を追加することはできません。集計関数を使用して重複レコードを検索し、削除してから、一意の制約を作成できます。

C. 複合ユニークインデックスを作成する

学生テーブルを作成する
 （
 学生ID int、id INT、
 sname VARCHAR(10)、
 スコア int、制約 uc_id UNIQUE(学生ID、id)
 )ENGINE=MyISAM デフォルト CHARSET=utf8;

D. 列の一意制約を削除する

テーブルスコアを変更し、インデックス uc_sname を削除します。

3. ドメインの整合性

1. デフォルト値

テーブルに新しいレコードを挿入するときに、フィールドに値が割り当てられていない場合、データベースシステムは自動的にフィールドにデフォルト値を割り当てます。

テーブル st を作成
(sid INT NULLでない主キー auto_increment、
sname varchar(10)、
件名varchar(20) デフォルト 'ソフトウェアエンジニアリング'、
TIMESTAMP のデフォルトを入力()
);

テーブルの列にデフォルト値制約を追加するには:

アラート テーブル st 列 subject VARCHAR(20) を変更します。デフォルトは 'Computer Science and Technology' です。

テーブル内の列からデフォルト値制約を削除するには:

アラートテーブル st 列 subject を変更します VARCHAR(20) デフォルト NULL;

2. 非NULL制約を作成する

非 NULL 制約は、現在の列の値が NULL でないことを確認するために使用されます。非 NULL 制約は、テーブルオブジェクトの列にのみ使用できます。

NULL 型の特性: int、float、その他のデータ型を含むすべての型の値は NULL になることができます。空の文字列は NULL と等しくなく、0 は NULL と等しくありません。

A. テーブルを作成するときに列に非NULL制約を指定する

テーブルスコアを作成する
 (sname VARCHAR(10) NULLではない、
 スコア int が NULL でない
 );

B. 指定された列に非NULL制約を指定する

アラート テーブル スコア変更列スコア int が NULL ではありません。

C. 非NULL制約を削除する

テーブルスコアを変更し、列スコア int を変更します。

3. 確認

check キーワードは、新しい行を挿入するとき、または既存の行を変更するときにのみ機能します。条件を満たさない値が列に入るのを防ぎます。null を挿入することは挿入しないことと同じなので、null 値には無効です。列には複数のチェックを設定できます。

age int check(年齢が10歳から20歳の間)

現在、MySQL はチェック制約をサポートしていません。Microsoft MSSQL はチェック制約をサポートしていますが、テーブルの作成時にチェック制約を指定することはできますが、機能しません。

IV. 参照整合性

1. 参照整合性の概要

MySQL の参照整合性は、通常、MySQL 外部キーを通じて実現されます。

外部キー (innoDB でのみサポート) によって参照されるテーブルの列は、主キーである必要があります。

外部キー宣言は 3 つの部分で構成されます。

A. どの列または列の組み合わせが外部キーであるか

B. 外部キーが参照するテーブルと列を指定する

C. 参照アクション [cascade (カスケード操作)、restrict (拒否操作)、set null (空に設定)、no action、set default]。

外部キー制約で参照アクションが指定されている場合、プライマリテーブルレコードが変更または削除されると、セカンダリテーブルによって参照される列はそれに応じて変更されるか、変更されないか、変更が拒否されるか、またはデフォルト値に設定されます。

参照先テーブルの列名は主キーである必要があり、参照先テーブルを削除する場合は参照関係を削除するか、現在のテーブルを削除する必要があります。

2. テーブル作成時に外部キーを指定する

学生テーブルとスコアテーブルの 2 つのテーブルを作成します。スコアテーブルの sid 列の値は学生テーブルを参照します (学生テーブルの sid 列は主キーとして設定され、テーブルのストレージエンジンは innodb であるため、スコアテーブルのストレージエンジンも innodb に設定する必要があります)。

学生テーブルを作成する
(sid int NULLでない主キー、
sname varchar(20)
) engine=innodb;テーブルスコアを作成
(sid int が null ではない、
mark INT、制約 score_fk FOREIGN KEY (sid) は、学生 (sid) を参照します (削除カスケード、更新カスケード) engine=innodb;

学生テーブルにレコードを挿入する

生徒の価値観に挿入（1、「孫悟空」）

成績テーブルにレコードを挿入します。学生番号は 1 で、成功です。

スコア値に挿入（1,98）

成績テーブルにレコードを挿入します。学生番号は 2 で、不合格です。

スコア値に挿入（2,88）

学生番号2のレコードを学生テーブルに挿入します。

学生の価値観に挿入する（2、「唐僧」）

次に、成績テーブルに学生番号 2 のレコードを挿入します。これは成功であり、外部キー参照が成功したことが証明されます。

スコア値に挿入（2,88）

3. 参照制約を削除する

テーブルスコアを変更し、外部キー score_fk を削除します。

4. 既存のテーブルに参照制約を追加する

テーブル score を変更し、制約 score_fk2 外部キー (sid) が student (sid) を参照することを追加します。

5.カスケードアクションの削除と更新を確認する

スコアテーブルに作成された参照整合性では、削除アクションと更新アクションの参照アクションにカスケードが選択されています。学生テーブルのsidが更新されると、スコアテーブルの対応するsidも更新されます。学生が削除されると、スコアテーブルに対応するsidのレコードも自動的に削除されます。

生徒テーブルを更新します。生徒番号が 1 の生徒を 10 に変更します。

学生セット sid = 10 を更新します (sid = 1)

スコアシートを確認すると、生徒番号 1 が 10 に変わっていることがわかります。

スコアから*を選択

学生テーブルで学生番号が2の学生を削除します。

sid = 2 の学生から削除

スコア表を見ると、生徒のスコアがカスケード削除されていることがわかります

スコアから*を選択

6. カスケードアクションの確認アクションなし

カスケードアクションは NO ACTION に設定されています。子テーブルに一致するレコードがある場合、親テーブルの対応する候補キーに対する更新/削除操作は許可されません。

制限アクションは、アクションなしと同じであり、どちらも外部キー制約を直ちにチェックします。

参照アクションをアクションなしに設定します。スコアテーブルに学生 SID がある場合、学生テーブルの学生 SID 列は変更できず、学生を削除できません。最初に生徒の成績を削除し、次に生徒を削除しない限り。

スコアテーブルの外部キー制約を削除します

テーブルスコアを変更し、外部キー score_fk を削除します。

スコアテーブルのsid列に外部キー制約を追加する

ALTER TABLE `score` ADD CONSTRAINT `score_fk` FOREIGN KEY (`sid`) REFERENCES `student` (`sid`) ON DELETE NO ACTION ON UPDATE NO ACTION;

学生ID 10の学生IDを更新できませんでした

学生セット sid = 11 を更新します (sid = 10)

生徒番号10の生徒を削除しました。失敗しました

sid = 10 の学生から削除

まず生徒のスコアテーブル内のレコードを削除し、次に生徒を削除する必要があります。

sid=10 の学生から削除、sid=10 のスコアから削除;

7. カスケードアクションを検証する NULLを設定する

親テーブルのレコードを更新/削除する場合、子テーブルの一致するレコードの列を null に設定します。子テーブルの外部キーは null 以外にすることはできないことに注意してください。

成績表の外部キー制約を削除する

テーブルスコアを変更し、外部キー score_fk を削除します。

成績表のsid列に外部キー制約を追加し、参照アクションをnullに設定するように設定します。

テーブル score を変更し、制約 score_fk 外部キー (sid) が student (sid) を参照し、削除時に null を設定し、更新時に null を設定します。

スコアテーブルのsid列のデフォルト値をNULLに変更します。

ALTER TABLE `score` MODIFY COLUMN `sid` INTEGER(11) DEFAULT NULL;学生の値に挿入 (1,'孙悟空')学生の値に挿入 (2,'猪八戒')スコアの値に挿入 (1,98)スコアの値に挿入 (2,88)

学生テーブルから学生番号1の学生を削除します。

delete from student where sid = 1

スコアテーブルを確認すると、スコアテーブルの学生番号1の列はNULLです。

select * from score

要約する

以上が、データベースの削除から実行までの MySQL_上級編 (I) - データ整合性についての編集者による紹介です。皆様のお役に立てれば幸いです。ご質問がございましたら、メッセージを残していただければ、編集者がすぐに返信いたします。また、123WORDPRESS.COM ウェブサイトをサポートしてくださっている皆様にも感謝申し上げます。

以下もご興味があるかもしれません: