How to implement MySQL foreign key association operation

MySQL’s foreign key constraints

Note that only MySQL’s InnoDB table engine supports foreign key associations, and MyISAM does not. SET FOREIGN_KEY_CHECKS = 0/1 can be used to manually turn on or off MySQL's foreign key constraints.

The biggest benefit of MySQL's foreign key constraints is that it can help us complete data consistency verification. Using the default RESTRICT foreign key type, reference validity checks will be performed when creating, modifying, or deleting records.

Assume that our database contains two tables: posts(id, author_id, content) and authors(id, name). When performing the following operations, the database will trigger the check of foreign keys:

When inserting data into the posts table, check whether the author_id exists in the authors table;

When modifying the data in the posts table, check whether the author_id exists in the authors table;

Delete When entering data in the authors table, check whether there is a foreign key referencing the current record in posts;

As a system specifically designed to manage data, the database can better ensure integrity compared with application services, and the above These operations are all extra work caused by introducing foreign keys, but this is also a necessary price for the database to ensure data integrity. We can conduct a simple quantitative analysis to understand the specific impact of introducing foreign keys on performance, rather than just a theoretical qualitative analysis.

Define foreign keys (References, reference) when creating a table

In the CREATE TABLE statement, specify the foreign key through the FOREIGN KEY keyword. The specific syntax format is as follows:

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

Example:

# 部门表 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;

NOTE: The foreign key of the secondary table must be related to the primary key of the primary table, and the data types of the primary key and the foreign key must be consistent.

After the above statement is successfully executed, a foreign key constraint named fk_emp_dept1 is added to the representation tb_emp6. The foreign key name is deptId, which depends on the primary key id of the table tb_dept1.

View the constraint information of the main table

MariaDB [test_db]> select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where REFERENCED_TABLE_NAME='tb_dept1'\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)

Modify the foreign key constraints of the original table

Foreign key constraints can also be added when modifying the table, but adding foreign key constraints is The premise is: the data in the foreign key column in the secondary table must be consistent with the data in the primary key column in the primary table or there is no data.

The syntax format for adding foreign key constraints when modifying the data table is as follows:

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

Example: Modify the data table tb_emp2, set the field deptId as a foreign key, and compare it with the primary key id of the data table tb_dept1 association.

# 创建 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

Delete foreign key constraints

When a foreign key constraint is not needed in a table, it needs to be deleted from the table. Once the foreign key is deleted, the association between the master table and the slave table will be released.

The syntax format for deleting foreign key constraints is as follows:

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

Example: Delete the foreign key constraint fk_tb_dept1 in the data table tb_emp2.

ALTER TABLE tb_emp2 DROP FOREIGN KEY fk_tb_dept1;
 
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`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312

The above is the detailed content of How to implement MySQL foreign key association operation. For more information, please follow other related articles on the PHP Chinese website!