How to add foreign keys in mysql

Mysql method to add foreign keys: 1. In the CREATE TABLE statement, add foreign keys through the FOREIGN KEY keyword; 2. In the ALTER TABLE statement, add foreign keys through the ADD and FOREIGN KEY keywords .

Recommended course: MySQL Tutorial.

MySQL foreign key constraint (FOREIGN KEY) is a special field of the table, often used together with primary key constraints. 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 the association between the master table and the slave table, establish a connection for the data in the two tables, and 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 come to the fruit stall to buy fruits, you can only choose apples, peaches, plums, and watermelons. Other fruits are not available for purchase.

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

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

• The main 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 main table.

• The primary key 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 contents of the foreign key are correct.

• Specify the column name or a 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 main table.

• The number of columns in the foreign key must be the same as the number of columns in the primary key of the main table.

• The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the main table.

Set foreign key constraints when creating the table

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

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

Example 1

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

The SQL statement and running results to create tb_dept1 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 the data table tb_emp6, and create a foreign key constraint on the table tb_emp6, so that its key deptId is associated with the primary key id of the 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 successfully executed, 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 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. For example, both are of type INT, or both are of type CHAR. If such requirements are not met, the "ERROR 1005 (HY000): Can't create table" error will occur when creating the slave table.

Add foreign key constraints when modifying the table

Foreign key constraints can also be added when modifying the table, but adding foreign keys The premise of the constraint 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 2

Modify the data table tb_emp2, set the field deptId as a foreign key, and match the data The primary key id of table tb_dept1 is associated. 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 the 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 the foreign key columns cannot be empty.

Delete foreign key constraints

When a foreign key constraint is no longer 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 3

Delete the foreign key constraint fk_tb_dept1 in the data table tb_emp2, SQL The statements and running results are shown below.

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 was deleted successfully.

The above is the detailed content of How to add foreign keys in mysql. For more information, please follow other related articles on the PHP Chinese website!