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

How to set foreign keys when building a table in mysql? This problem may be often seen in our daily study or work. I hope you can gain a lot from this question. The following is the reference content that the editor brings to you, let's take a look at it!

The method of setting the foreign key when creating the mysql table: in the "CREATE TABLE" statement, through the "[CONSTRAINT] FOREIGN KEY field name [, field name 2, …] REFERENCES primary key column 1 [, primary key column 2, …]" Statement settings.

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

Set foreign key constraints when creating a table

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

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

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.

Field name data type remarks idINT (11) department number nameVARCHAR (22) department name locationVARCHAR (22) department location

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.

Thank you for reading! After reading the above, do you have a general understanding of how to set foreign keys when building a table in mysql? I hope the content of the article will be helpful to all of you. If you want to know more about the relevant articles, you are welcome to follow the industry information channel.

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.