Shulou(Shulou.com)06/01 Report--

Today, I will talk to you about how to write mysql foreign key constraints, many people may not know much about it. In order to make you understand better, the editor has summarized the following for you. I hope you can get something according to this article.

Mysql foreign key constraint is written as follows: [[CONSTRAINT] FOREIGN KEY field name REFERENCES primary key column 1]. A foreign key constraint is a special field of a table and is often used with primary key constraints.

In the CREATE TABLE statement, the foreign key is specified through the FOREIGN KEY keyword.

The specific syntax format is as follows:

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

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).

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.

For example:

To show the foreign key relationship between the table and the table, this example creates a department table tb_dept1 in the test_db database with the following table structure.

The SQL statement to create the tb_dept1 and the result of the run are shown below.

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,SQL statement of the table tb_dept1 as the foreign key and the running result is shown below.

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, and 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 primary table, and the data types of the primary key and the foreign key must be the same. For example, both are INT types, or both are CHAR types. If this requirement is not met, the "ERROR 1005 (HY000): Can't create table" error occurs when creating the slave table.

After reading the above, do you have any further understanding of how to write mysql foreign key constraints? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.