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

Editor to share with you how to add primary key constraints when mysql modifies the table. I hope you will gain a lot after reading this article. Let's discuss it together.

In mysql, you can add a primary key constraint when modifying a data table through the "ALTER TABLE table name ADD PRIMARY KEY;" statement; when you modify a table to set a primary key constraint for a field in the 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.

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

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

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)

Add a primary key constraint when modifying a 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 ()

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.

Example

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

Mysql > DESC tb_emp2 +-+-+ | Field | Type | Null | Key | Default | Extra | + -+ | id | int (11) | NO | | NULL | name | varchar (30) | YES | | NULL | | deptId | int (11) | YES | | NULL | | salary | float | YES | | NULL | | +-- -+ 4 rows in set (0.14 sec)

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

Mysql > ALTER TABLE tb_emp2-> ADD PRIMARY KEY (id); Query OK, 0 rows affected (0.94 sec) Records: 0 Duplicates: 0 Warnings: 0mysql > DESC tb_emp2 +-+-+ | 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) finished reading this article I believe you have a certain understanding of how to add primary key constraints when mysql modifies the table. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!

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.