MySQL foreign key constraint (FOREIGN KEY) case explanation

MySQL foreign key constraint (FOREIGN KEY) is a special field of a table, often used with primary key constraint. For two tables with an associated relationship, the table where the primary key in the associated field is located is the primary table (parent table), and the table where the foreign key is located is the secondary table (child table).

Foreign keys are used to establish an association between the primary table and the secondary table, to connect the data in the two tables, and to constrain the consistency and integrity of the data in the two tables. For example, a fruit stall only has four kinds of fruits: apples, peaches, plums, and watermelons. Then, when you go to the fruit stall to buy fruit, you can only choose apples, peaches, plums, and watermelons. Other fruits cannot be purchased.

When a record is deleted from the main table, the corresponding record in the table must also be changed accordingly. A table can have one or more foreign keys, and foreign keys can be null values. If they are not null values, the value of each foreign key must be equal to a value of the primary key in the main table.

When defining foreign keys, you need to follow the following rules:

• The primary table must already exist in the database, or be the table currently being created. If it is the latter case, the master table and the slave table are the same table. Such a table is called a self-referential table, and this structure is called self-referential integrity.
• A primary key must be defined for the primary table.
• Primary keys cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as every non-null value of the foreign key appears in the specified primary key, the content of this foreign key is correct.
• Specify a column name or combination of column names after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the primary table.
• The number of columns in the foreign key must be the same as the number of columns in the primary key of the primary table.
• The data type of the foreign key column must be the same as the data type of the corresponding column in the primary key of the primary table.

Set foreign key constraints when creating a table

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

[CONSTRAINT <foreign key name>] FOREIGN KEY field name [, field name 2, ...]
REFERENCES <primary table name> primary key column 1 [, primary key column 2, …]

Example 1

In order to show the foreign key relationship between tables, this example creates a department table tb_dept1 in the test_db database. The table structure is shown in the following table.

The SQL statement for creating tb_dept1 and the running result are as follows.

mysql> CREATE TABLE tb_dept1
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)

Create a data table tb_emp6 and create a foreign key constraint on table tb_emp6. Let its key deptId be associated with the primary key id of table tb_dept1 as a foreign key. The SQL statement and running results are as follows.

mysql> 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)
    -> );
Query OK, 0 rows affected (0.37 sec)

mysql> DESC tb_emp6;
+--------+-------------+------+-----+---------+-------+
| 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 | |
+--------+-------------+------+-----+---------+-------+
4 rows in set (1.33 sec)

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

Note: The foreign key of the slave table must be associated with the primary key of the master table, and the data types of the primary key and foreign key must be consistent. For example, both are of type INT, or both are of type CHAR. If this requirement is not met, the error "ERROR 1005(HY000): Can't create table" will occur when creating a slave table.

Adding foreign key constraints when modifying a table

Foreign key constraints can also be added when modifying a table, but the prerequisite for adding a foreign key constraint is that the data in the foreign key column of the slave table must be consistent with the data in the primary key column of the master table or there must be no data.

The syntax for adding a foreign key constraint when modifying a data table is as follows:

ALTER TABLE <table name> ADD CONSTRAINT <foreign key name>
FOREIGN KEY(<column name>) REFERENCES <primary table name> (<column name>);

Example 2

Modify the data table tb_emp2, set the field deptId as a foreign key, and associate it with the primary key id of the data table tb_dept1. The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)
    -> REFERENCES tb_dept1(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) 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
1 row in set (0.12 sec)

Note: When adding foreign key constraints to an already created data table, make sure that the values ​​of the columns to which the foreign key constraints are added all come from the primary key columns, and that the foreign key columns cannot be empty.

Dropping a foreign key constraint

When a foreign key constraint is no longer needed in a table, it needs to be removed from the table. Once the foreign key is deleted, the association between the primary table and the secondary table will be removed.

The syntax for dropping a foreign key constraint is as follows:

ALTER TABLE <table name> DROP FOREIGN KEY <foreign key constraint name>;

Example 3

Delete the foreign key constraint fk_tb_dept1 in the data table tb_emp2. ​​The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_emp2
    -> DROP FOREIGN KEY fk_tb_dept1;
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.00 sec)

It can be seen that FOREIGN KEY no longer exists in tb_emp2, and the original foreign key constraint named fk_emp_dept has been deleted successfully.

This is the end of this article about the case study of MySQL foreign key constraints (FOREIGN KEY). For more relevant MySQL foreign key constraints (FOREIGN KEY) content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!