Specific method to add foreign key constraints in mysql

The operating environment of this tutorial: Windows 7 system, MySQL 8 version, Dell G3 computer.

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.

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.

Adding foreign key constraints to a table in mysql

Foreign key constraints can be added when modifying a table, but the prerequisite for adding foreign key constraints 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

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

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.

Content extension:

When to use foreign key constraints

To be honest, you don’t necessarily need to use foreign key constraints when using InnoDB tables in MySQL. However, in order to illustrate the function of foreign key constraints in some cases, we will use the code of the example mentioned above to explain it in detail. It includes two MyISAM tables, one for storing blog posts and one for storing comments.

When defining the database schema, we will establish a one-to-many relationship between these two tables by creating a foreign key in the comments table to map each row (i.e., comment) to a specific blog post. Here is the basic SQL code to create a sample MyISAM table:

DROP TABLE IF EXISTS `test`.`blogs`;

CREATE TABLE `test`.`blogs` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`title` TEXT,

`content` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIROSE KEY (`id`)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;


DROP TABLE IF EXISTS `test`.`comments`;

CREATE TABLE `test`.`comments` (

`id` INT(10) UNSIGNED AUTO_INCREMENT,

`blog_id` INT(10) UNSIGNED DEFAULT NULL,

`comment` TEXT,

`author` VARCHAR(45) DEFAULT NULL,

PRIROSE KEY (`id`)

)ENGINE=MyISAM DEFAULT CHARSET=utf8;

This is the end of this article about the specific method of adding foreign key constraints to MySQL. For more information about how to add foreign key constraints to MySQL, 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!