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

How to set the primary key when creating 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!

Setting method: in the "CREATE TABLE" statement, specify the primary key through the "PRIMARY KEY" keyword, syntax format "field name data type PRIMARY KEY [default]" or "[CONSTRAINT constraint name] PRIMARY KEY field name".

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.

Set primary key constraints when creating a table

When you set a primary key constraint when you create a data table, you can set either a primary key for one field in the table or a federated primary key for multiple fields in the table. But no matter which method is used, there can be only one primary key in a table. The following describes how to set up a single-field primary key and a multi-field joint primary key.

1) set single-field primary key

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.

Mysql > CREATE TABLE tb_emp3-> (- > id INT (11) PRIMARY KEY,-> name VARCHAR (25),-> deptId INT (11),-> salary FLOAT->); Query OK, 0 rows affected (0.37 sec) mysql > DESC tb_emp3 +-+-+ | 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)

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.

Mysql > CREATE TABLE tb_emp4-> (- > 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_emp4 +-+-+ | 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) set the federated primary key when creating the table

The so-called federated primary key is that the primary key is made up of multiple fields in a table.

For example, when setting up the student course selection data table, do you use the student number as the primary key or the course number as the primary key? If you use the student number as the key, then a student can only choose one course. If you use the course number as the key, only one student can take a course. Obviously, both of these situations are not in line with the actual situation.

In fact, the design of students' course selection schedule should be limited to the fact that a student can only choose the same course once. Therefore, the student number and course number can be put together as the primary key, which is the joint primary key.

The primary key is composed of multiple fields, and the syntax format is as follows:

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

Note: when the primary key consists of multiple fields, you cannot declare the primary key constraint directly after the field name.

Example 3

Create the data table tb_emp5, 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. The SQL statement and run results are as follows.

Mysql > CREATE TABLE tb_emp5-> (- > name VARCHAR (25),-> deptId INT (11),-> salary FLOAT,-> PRIMARY KEY (id,deptId)->); Query OK, 0 rows affected (0.37 sec) mysql > DESC tb_emp5 +-+-+ | 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! After reading the above, do you have a general idea of how to set the primary key when creating 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.