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

This article is about how to add primary keys to mysql. The editor thought it was very practical, so I shared it with you as a reference. Let's follow the editor and have a look.

The full name of "PRIMARY KEY" is "primary key constraint". A MySQL primary key constraint is a column or a combination of columns whose values uniquely identify each row in the table. Such a column or columns is called the primary key of the table, through which the physical integrity of the table can be enforced. So how do you add primary key constraints in MySQL? The following article will introduce you.

A primary key constraint defines a primary key in a table to uniquely determine the identifier of each row of data in the table. A primary key can be a column or a combination of multiple columns in a table, where a primary key composed of multiple columns is called a compound primary key. The primary key should follow the following rules:

● can define only one primary key per table.

The ● primary key value must uniquely identify each row in the table and cannot be NULL, that is, it is impossible for two rows of data in the table to have the same primary key value. This is the principle of uniqueness.

● A column name can only appear once in the list of compound primary keys.

The ● compound primary key cannot contain unnecessary redundant columns. When a column of a compound primary key is deleted, if the primary key formed by the remaining columns still satisfies the uniqueness principle, then the compound primary key is incorrect. This is the principle of minimization.

1. Add a primary key constraint when creating a table

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

Specify the primary key while defining the column. The syntax rules are as follows:

PRIMARY KEY [default]

Example: create a tb_emp 1 data table in the test_db database, whose primary key is id

Mysql > CREATE TABLE tb_emp1-> (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25),-> deptId INT (11),-> salary FLOAT->); Query OK, 0 rows affected (0.37 sec) mysql > DESC tb_emp1 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (11) | NO | PRI | NULL | name | varchar (25) | YES | | NULL | | deptId | int (11) | YES | | NULL | | salary | float | YES | NULL | | + -+ 4 rows in set (0.14 sec)

After all the columns are defined, the syntax format for the specified primary key is:

[CONSTRAINT] PRIMARY KEY [field name]

Example: create a tb_emp 2 data table in a test_db database with the primary key id

Mysql > CREATE TABLE tb_emp2-> (- > id INT (11),-> name VARCHAR (25),-> deptId INT (11),-> salary FLOAT,-> PRIMARY KEY (id)->); Query OK, 0 rows affected (0.37 sec) mysql > DESC tb_emp2 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (11) | NO | PRI | NULL | name | varchar (25) | YES | | NULL | | deptId | int (11) | YES | | NULL | | salary | float | YES | NULL | | + -+ 4 rows in set (0.14 sec)

2. Add primary key constraints after creating the table

After you create a table, you can add a primary key constraint when you modify the data table, and the syntax rule is:

ALTER TABLE ADD PRIMARY KEY ()

Example: modify the datasheet tb_emp3 and set the field id as the primary key

Mysql > ALTER TABLE tb_emp3-> ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.94 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp3 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (11) | NO | PRI | NULL | name | varchar (30) | YES | | NULL | | deptId | int (11) | YES | | NULL | | salary | float | YES | NULL | | + -+ 4 rows in set (0.12 sec)

3. Set compound primary key

You can also add a compound primary key when you create a table, where the primary key is composed of multiple fields, and the syntax rules are as follows:

PRIMARY KEY [Field 1, Field 2, … , field n]

Example: create a data table tb_emp4, assuming that there is no primary key id in the table. In order to uniquely identify an employee, you can combine name and deptId as the primary key.

Mysql > CREATE TABLE tb_emp4-> (- > name VARCHAR (25),-> deptId INT (11),-> salary FLOAT,-> PRIMARY KEY (id,deptId)->); Query OK, 0 rows affected (0.37 sec) mysql > DESC tb_emp4 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | name | varchar (25) | NO | PRI | NULL | deptId | int (11) | NO | PRI | NULL | | salary | float | YES | | NULL | | +-+-+ 3 rows in Set (0.14 sec) Thank you for your reading! On the method of adding primary keys to mysql to share here, I hope the above content can be of some help to you, so that you can learn more knowledge. If you think the article is good, you can share it and let more people see it.

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.