Shulou(Shulou.com)05/31 Report--

Editor to share with you how mysql increases foreign key constraints. I hope you will get something after reading this article. Let's discuss it together.

Mysql can use "ALTER TABLE" statements and "FOREIGN KEY" keywords to add foreign keys, syntax "ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (column name) REFERENCES main table name (column name);"

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

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

Foreign keys are used to establish the relationship between the master table and the slave table, to establish a connection between the data of the two tables, and to restrict the consistency and integrity of the data in the two tables.

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

The master table must already exist in the database or the table that is currently being created. In the latter case, the master table and the slave table are the same table, and such a table is called a self-reference table, and this structure is called self-referential integrity.

You must define a primary key for the primary table.

The primary key cannot contain null values, but null values are allowed in foreign keys. That is, as long as each non-null value of the foreign key appears in the specified primary key, the content of the foreign key is correct.

Specify a 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 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 column in the foreign key must be the same as the corresponding column in the primary key of the primary table.

Mysql adds foreign key constraints to the table

Foreign key constraints can be added when modifying the table, but the premise of 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 in the primary table or there is no data.

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

ALTER TABLE ADD CONSTRAINT FOREIGN KEY () REFERENCES ()

Example

Modify the data table tb_emp2, set the field deptId to the 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: 0mysql > SHOW CREATE TABLE tb_emp2\ Graph * 1. Row * * Table: tb_emp2Create 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=gb23121 row in set (0.12 sec)

Note: when adding a foreign key constraint to a data table that has been created, make sure that the values of the columns that add the foreign key constraint are all derived from the primary key column, and that the foreign key column cannot be empty.

After reading this article, I believe you have a certain understanding of "how to increase foreign key constraints in mysql". If you want to know more about it, you are welcome to follow the industry information channel. Thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.