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

MySQL is a relational database management system in which relational databases store data in different tables instead of all data in one large warehouse, which increases speed and flexibility.

The SQL language used by MySQL is the most commonly used standardized language for accessing databases. MySQL software adopts the dual licensing policy, which is divided into community version and commercial version. Because of its small size, high speed and low total cost of ownership, especially open source, the development of small and medium-sized websites generally choose MySQL as the website database.

We can set foreign key constraints when we create the 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, …]

For example:

Now create a department table tb_dept1 in the test_db database with the structure shown in the following figure:

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.

The above is the details of how to create mysql foreign key constraints, please pay attention to other related articles!

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.