MySQL 外部キー関連付け操作を実装する方法-mysql チュートリアル-php.cn

MySQL 外部キー関連付け操作を実装する方法

王林

リリース： 2023-06-03 09:40:37

転載

1238 人が閲覧しました

MySQL の外部キー制約

MySQL の InnoDB テーブルエンジンのみが外部キーの関連付けをサポートしており、MyISAM はサポートしていないことに注意してください。 SET FOREIGN_KEY_CHECKS = 0/1 を使用すると、MySQL の外部キー制約を手動でオンまたはオフにすることができます。

MySQL の外部キー制約の最大の利点は、データの整合性検証を完了できることです。デフォルトの RESTRICT 外部キータイプを使用すると、レコードの作成、変更、または削除時に参照の有効性チェックが実行されます。

データベースに、posts(id, author_id, content) と authors(id, name) という 2 つのテーブルが含まれているとします。次の操作を実行すると、データベースは外部キーのチェックをトリガーします:

posts テーブルにデータを挿入するときは、author_id が authors テーブルに存在するかどうかを確認してください。

posts テーブルのデータを変更するときは、author_id が authors テーブルに存在するかどうかを確認してください。

削除 authors テーブルにデータを入力するときは、投稿の現在のレコードを参照する外部キーがあるかどうかを確認してください;

データを管理するために特別に設計されたシステムとして、データベースはアプリケーションサービスと比較して整合性をより確実に確保できます。および上記これらの操作はすべて外部キーの導入によって生じる追加作業ですが、これはデータベースのデータ整合性を確保するために必要な代償でもあります。単なる理論的な定性分析ではなく、単純な定量分析を実行して、外部キーの導入がパフォーマンスに与える具体的な影響を理解できます。

テーブル作成時に外部キー (参照、参照) を定義する

CREATE TABLE ステートメントで、FOREIGN KEY キーワードを使用して外部キーを指定します。具体的な構文形式は次のとおりです:

[CONSTRAINT <外键名>] FOREIGN KEY 字段名 [，字段名2，…] REFERENCES <主表名> 主键列1 [，主键列2，…]

ログイン後にコピー

例:

# 部门表 tb_dept1（主表）
CREATE TABLE tb_dept1
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(22) NOT NULL,
    location VARCHAR(50)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
 
# 员工表 tb_emp6（从表），创建外键约束，让 deptId 作为外键关联到 tb_dept1 的主键 id。
CREATE TABLE tb_emp6
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT,
    CONSTRAINT fk_emp_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

ログイン後にコピー

注: セカンダリテーブルの外部キーはプライマリテーブルの主キーに関連付けられている必要があり、主キーと外部キーのデータ型は一貫している必要があります。。

上記のステートメントが正常に実行された後、fk_emp_dept1 という名前の外部キー制約が表現 tb_emp6 に追加されます。外部キー名は deptId で、テーブル tb_dept1 の主キー ID に依存します。

メインテーブルの制約情報の表示

MariaDB [test_db]> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME=&#39;tb_dept1&#39;\G;
*************************** 1. row ***************************
           CONSTRAINT_CATALOG: def
            CONSTRAINT_SCHEMA: test_db
              CONSTRAINT_NAME: fk_emp_dept1
                TABLE_CATALOG: def
                 TABLE_SCHEMA: test_db
                   TABLE_NAME: tb_emp6
                  COLUMN_NAME: deptId
             ORDINAL_POSITION: 1
POSITION_IN_UNIQUE_CONSTRAINT: 1
      REFERENCED_TABLE_SCHEMA: test_db
        REFERENCED_TABLE_NAME: tb_dept1
       REFERENCED_COLUMN_NAME: id
1 row in set (0.00 sec)

ログイン後にコピー

元のテーブルの外部キー制約の変更

テーブルの変更時に外部キー制約も追加できますが、外部キー制約は次のとおりです。前提条件は、セカンダリテーブルの外部キーカラムのデータがプライマリテーブルの主キーカラムのデータと一致している必要があり、そうでない場合はデータがありません。

データテーブルを変更するときに外部キー制約を追加するための構文形式は次のとおりです。

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名> FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

ログイン後にコピー

例: データテーブル tb_emp2 を変更し、フィールド deptId を外部キーとして設定し、比較します。データテーブル tb_dept1 関連付けの主キー ID を使用します。

# 创建 tb_emp2（从表）
CREATE TABLE tb_emp2
(
    id INT(11) PRIMARY KEY,
    name VARCHAR(25),
    deptId INT(11),
    salary FLOAT
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;
 
MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  |     | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
 
# 添加外键约束
ALTER TABLE tb_emp2 ADD CONSTRAINT fk_tb_dept1 FOREIGN KEY(deptId) REFERENCES tb_dept1(id);
 
MariaDB [test_db]> desc tb_emp2;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | NO   | PRI | NULL    |       |
| name   | varchar(25) | YES  |     | NULL    |       |
| deptId | int(11)     | YES  | MUL | NULL    |       |
| salary | float       | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+
 
MariaDB [test_db]> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

ログイン後にコピー

外部キー制約の削除

テーブル内で外部キー制約が不要な場合は、テーブルから削除する必要があります。外部キーを削除すると、マスターテーブルとスレーブテーブルの関連付けが解除されます。

外部キー制約を削除するための構文形式は次のとおりです。

ALTER TABLE <表名> DROP FOREIGN KEY <外键约束名>;

ログイン後にコピー

例: データテーブル tb_emp2 内の外部キー制約 fk_tb_dept1 を削除します。

rree

以上がMySQL 外部キー関連付け操作を実装する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。