How to realize the Primary Foreign key constraint by mysql 04/02 Update SLTechnology News&Howtos

How to realize the Primary Foreign key constraint by mysql

2025-04-02 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

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

This article will explain in detail how to achieve primary and foreign key constraints in mysql. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Mysql primary key constraint is written as "CREATE TABLE table name (field name data type PRIMARY KEY)" and mysql foreign key constraint is written as "CREATE TABLE table name (FOREIGN KEY field name REFERENCES primary key column)".

Mysql primary key constraint

The full name of primary key (PRIMARY KEY) is "primary key constraint", which is the most frequently used constraint in MySQL. In general, in order to make it easier for DBMS to find records in the table faster, a primary key is set in the table.

1. Set the primary key constraint when creating the table

In the CREATE TABLE statement, the primary key is specified by the PRIMARY KEY keyword.

Specify the primary key while defining the field. The syntax format is as follows:

PRIMARY KEY [default]

Example 1

Create a tb_emp3 data table in the test_db database with the primary key id,SQL statement and the run result as follows.

Or specify the primary key after all the fields have been defined. The syntax format is as follows:

[CONSTRAINT] PRIMARY KEY [field name]

Example 2

Create a tb_emp4 data table in the test_db database with the primary key id,SQL statement and the run result as follows.

2. Add primary key constraints when modifying the table

Primary key constraints can not only be created while the table is created, but can also be added when the table is modified. It is important to note, however, that null values are not allowed in fields that are set as primary key constraints.

The syntax format for adding primary key constraints when modifying a datasheet is as follows:

ALTER TABLE ADD PRIMARY KEY ()

View the table structure, SQL statements, and run results of the tb_emp2 data table as shown below.

Example 3

Modify the data table tb_emp2 and set the field id as the primary key. The SQL statement and run result are as follows.

In general, when you set a primary key constraint for a field in a table when you modify a table, make sure that the values in the fields that are set as primary key constraints cannot be duplicated and that they are not empty. Otherwise, the primary key constraint cannot be set.

Mysql foreign key constraint

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. For example, a fruit stall, only apples, peaches, plums, watermelons and other four kinds of fruit, then you come to the fruit stall to buy fruit, you can only choose apples, peaches, plums and watermelons, other fruits can not be bought.

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.

1. 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 4

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.

2. Add foreign key constraints when modifying the table

Foreign key constraints can also 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 5

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. The SQL statement and run results are shown below.

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.

On how mysql to achieve the primary foreign key constraints to share here, I hope that the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.