外部キー制約の機能:
外部キー制約のいくつかの重要な機能を以下に説明します。
- 子テーブルで使用される外部キーのデータ型は、外部キーを参照するために親テーブルで使用される主キーのデータ型と同じである必要があります。
- 任意のインデックス列または複数の列は、InnoDBテーブルの外部キーとしてのみ参照できます。
- 外部キーを作成するには、参照特権、またはSELECT、INSERT、UPDATE、およびDELETEステートメントの特権の少なくとも1つが必要です。
- 外部キーは2つの方法で作成できます。 1つはCREATEステートメントを使用し、もう1つはALTERステートメントを使用します。
前提条件:
外部キー制約を作成する前に、主キーを使用してデータベースと親テーブルを作成する必要があります。データベース名が「 図書館 ’であり、‘という名前の2つの親テーブルが含まれています 本 ' と ' 借り手 ’。を使用してMySQLサーバーと接続します mysql クライアントを作成し、次のSQLステートメントを実行して、データベースとテーブルを作成します。
作成 データベース 図書館;
使用する 図書館;
作成 テーブル 本((
id INT いいえ ヌル 自動増加 、
題名 varchar ((50)。 いいえ ヌル 、
著者 varchar ((50)。 いいえ ヌル 、
出版社 varchar ((50)。 いいえ ヌル 、
主キー ((id)。
)。 エンジン = INNODB ;
作成 テーブル 借り手((
id VARCHAR ((50)。 いいえ ヌル 、
名前 varchar ((50)。 いいえ ヌル 、
住所 varchar ((50)。 いいえ ヌル 、
Eメール varchar ((50)。 いいえ ヌル 、
主キー ((id)。
)。 エンジン = INNODB ;
CREATEステートメントを使用して外部キー制約を定義する
‘という名前のテーブルを作成します book_borrow_info 次のステートメントを実行して、外部キー制約を使用します。ここでは、 book_id フィールドは 外部キー このテーブルとこのフィールドのすべての値は、 id の分野 本 テーブル。 本 親テーブルであり、 book_borrow_info 子テーブルです。ここでは、外部キーにも2つの制限が設定されています。これらは カスケードを削除 と カスケードの更新 。つまり、主キーが親テーブルから削除または更新される場合、外部キーに関連する子テーブルに関連する対応するレコードが削除されるか、外部キーが更新されます。
作成 テーブル book_borrow_info((
Borrow_id VARCHAR ((50)。、
book_id INT 、
借りる日付 日にち いいえ ヌル 、
return_date 日にち いいえ ヌル 、
状態 VARCHAR ((15)。 いいえ ヌル 、
索引 par_ind((book_id)。、
主キー ((Borrow_id、借りる日付)。、
外部キー ((book_id)。 参考文献 本((id)。
オン 消去 カスケード
オン アップデート カスケード
)。 エンジン = INNODB ;
次に、次のSQLステートメントを実行して、両方のテーブルにいくつかのレコードを挿入します。最初のINSERTステートメントは、4つのレコードをに挿入します 本 テーブル。の4つの値 id の分野 本 自動インクリメント属性のテーブルは1、2、3、4になります。 2番目のINSERTステートメントは、4つのレコードをに挿入します。 book_borrow_info に基づく id の値 本 テーブル。
入れる の中へ 本 値
(( ヌル 、 「モッキンバードを殺すために」、 「ハーパー・リー」、 「グランドセントラルパブリッシング」)。、
(( ヌル 、 「百年の孤独」、 「ガルシアマルケス」、 「ルトフィ・オズコック」)。、
(( ヌル 、 「インドへの道」、 「フォースター、E.M。」、 「BBCハルトン画像ライブラリ」)。、
(( ヌル 、 '透明人間'、 「ラルフ・エリソン」、 「ブリタニカ百科事典」)。;
入れる の中へ book_borrow_info 値
((「123490」、 1、 「2020-02-15」、 「2020-02-25」、 '戻ってきた')。、
((「157643」、 2、 「2020-03-31」、 「2020-03-10」、 '保留中')。、
((「174562」、 4、 「2020-04-04」、 '2020-04-24'、 '借りた')。、
((「146788」、 3、 「2020-04-10」、 「2020-01-20」、 '借りた')。;
親テーブルの主キーフィールドに存在しない子テーブルの外部キーフィールドに値を挿入しようとすると、MySQLはエラーを生成します。次のSQLステートメントは、親テーブルが 本 ID値が含まれていません 10 。
入れる の中へ book_borrow_info 値((「195684」、 10、 「2020-04-15」、 「2020-04-30」、 '戻ってきた')。;
次のDELETEステートメントを実行した後、4番目のレコードが 本 次に、テーブルからの関連レコード book_borrow_info テーブルは、外部キー制約のために自動的に削除されます。
消去 から 本 どこ id= 4;選択する * から 本;
選択する * から book_borrow_info;
ALTERステートメントを使用して外部キー制約を定義する
最初に、いくつかのレコードをに挿入します 借り手 テーブルとこのテーブルは、次の親テーブルとして定義されます 年 声明。
入れる の中へ 借り手 値((「123490」、 「パトリック・ウッド」、 '34 West Street LANCASTER LA14 9ZH '、 '[メール保護]')。、
((「157643」、 「エズラ・マーティン」、 '10 The Grove BIRMINGHAM B98 1EU '、 '[メール保護]')。、
((「174562」、 「ジョン・インズ・アーチー」、 '55 Main Road LIVERPOOL L2 3OD '、 '[メール保護]')。、
((「146788」、 「フレデリック・ハンソン」、 '85 Highfield Road SHREWSBURY SY46 3ME '、 '[メール保護]')。;
以下を実行します 年 別の外部キー制約を設定するステートメント book_borrow_info との関係を作るためのテーブル 借り手 テーブル。ここ、 Borrow_id の外部キーとして定義されています book_borrow_info テーブル。
ALTER TABLE book_borrow_info ADD CONSTRAINT fk_borrower外部キー((Borrow_id)。参考資料借り手((id)。更新制限のカスケードの削除時;
次に、レコードをに挿入します book_borrow_info 有効な Borrow_id に存在する値 id の分野 借り手 テーブル。 157643 値が借り手テーブルに存在し、次のINSERTステートメントが正常に実行されます。
入れる の中へ book_borrow_info 値((「157643」、 1、 「2020-03-10」、 「2020-03-20」、 '戻ってきた')。;
次のINSERTステートメントは、ID値が原因でエラーメッセージを生成します。 195680 借り手テーブルには存在しません。
入れる の中へ book_borrow_info 値((「195680」、 1、 「2020-04-15」、 「2020-04-30」、 '戻ってきた')。;
結論:
外部キー制約を適切に定義することは、リレーショナルデータベースを作成し、テーブル間のデータを適切に管理するための非常に重要なタスクです。外部キー制約の使用法を知ることは、データベース設計者にとって非常に重要です。この記事が、新しいデータベース設計者が外部キー制約の概念を理解し、それらをタスクに適切に適用するのに役立つことを願っています。