How to set mysql primary key non-null constraint?

In mysql, you can set primary key constraints by using the " PRIMARY KEY [default value]" statement in the "CREATE TABLE" statement, using " NOT NULL" statement to set a non-null constraint.

#mysql primary key constraint

The full name of PRIMARY KEY is "primary key constraint", which is the most frequently used constraint in MySQL. Under normal circumstances, in order to facilitate the DBMS to find records in the table faster, a primary key will be set in the table.

The following points should be noted when using primary keys:

• Each table can only define one primary key.

• The primary key value must uniquely identify each row in the table and cannot be NULL, that is, there cannot be two rows of data with the same primary key value in the table. This is the principle of uniqueness.

• A field name can only appear once in the joint primary key field table.

• The joint primary key cannot contain unnecessary redundant fields. When a field in the joint primary key is deleted, if the primary key composed of the remaining fields still satisfies the uniqueness principle, then the joint primary key is incorrect. This is the principle of minimization.

Set the primary key constraint when creating the table

Set the primary key constraint when creating the data table. You can also set the primary key for a field in the table. , you can also set a joint primary key for multiple fields in the table. But no matter which method is used, there can only be one primary key in a table. The following explains how to set a single-field primary key and a multi-field joint primary key.

1) Set a single-field primary key

In the CREATE TABLE statement, specify the primary key through the PRIMARY KEY keyword.

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

<字段名> <数据类型> PRIMARY KEY [默认值]

Example 1

Create the tb_emp3 data table in the test_db database, whose primary key is id, SQL statement and The running results are 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 defining all fields. The syntax format is as follows:

[CONSTRAINT <约束名>] PRIMARY KEY [字段名]

Example 2

Create the tb_emp4 data table in the test_db database, and its primary key is id , the SQL statements and running results are 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 joint primary key when creating the table

The so-called joint primary key means that the primary key is composed of multiple fields in a table.

For example, when setting up a student course selection data table, should the student number be used as the primary key or the course number as the primary key? If the student number is used as the primary key, then a student can only choose one course. If the course number is used as the primary key, then only one student can choose a course. Obviously, both of these situations are unrealistic.

In fact, when designing a student course selection schedule, the limitation is 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 a joint primary key.

The primary key is composed of multiple fields. The syntax format is as follows:

PRIMARY KEY [字段1，字段2，…,字段n]

Note: When the primary key is composed of multiple fields, the primary key constraint cannot be declared directly after the field name.

Example 3

Create the data table tb_emp5. Assume 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 running 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)

Add primary key constraints when modifying the table

Primary key constraints can not only be created when the table is created, but can also be added when the table is modified. However, it should be noted that null values ​​are not allowed in fields set as primary key constraints.

The syntax format for adding primary key constraints when modifying the data table is as follows:

ALTER TABLE <数据表名> ADD PRIMARY KEY(<字段名>);

View the table structure of the tb_emp2 data table. The SQL statement and running results are as follows.

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)

Example 4

Modify the data table tb_emp2 and set the field id as the primary key. The SQL statement and running results 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: 0
mysql> 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)

Normally, when you want to set the primary key constraint of a field in the table when modifying the table, you must ensure that the values ​​in the field set as the primary key constraint cannot have duplicates and must be non-empty. . Otherwise, the primary key constraint cannot be set.

mysql non-null constraint

MySQL non-null constraint (NOT NULL) means that the value of the field cannot be null. For fields that use non-null constraints, if the user does not specify a value when adding data, the database system will report an error. This can be achieved with the CREATE TABLE or ALTER TABLE statement. Add the keyword NOT NULL as a qualifier after the definition of a column in the table to constrain the value of the column to not be empty.

For example, in the user information table, if the user name is not added, then this user information will be invalid. At this time, you can set a non-null constraint for the user name field.

Set non-null constraints when creating a table

You can use the NOT NULL keyword to set non-null constraints when creating a table. The specific syntax format is as follows:

<字段名> <数据类型> NOT NULL

Example 1

Create data table tb_dept4. The specified department name cannot be empty. The SQL statement and running results are as follows.

mysql> CREATE TABLE tb_dept4
    -> (
    -> id INT(11) PRIMARY KEY,
    -> name VARCHAR(22) NOT NULL,
    -> location VARCHAR(50)
    -> );
Query OK, 0 rows affected (0.37 sec)
mysql> DESC tb_dept3;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id       | int(11)     | NO   | PRI | NULL    |       |
| name     | varchar(22) | NO   |     | NULL    |       |
| location | varchar(50) | YES  |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
3 rows in set (0.06 sec)

在修改表时添加非空约束

如果在创建表时忘记了为字段设置非空约束，也可以通过修改表进行非空约束的添加。

修改表时设置非空约束的语法格式如下：

ALTER TABLE <数据表名>
CHANGE COLUMN <字段名>
<字段名> <数据类型> NOT NULL;

例 2

修改数据表 tb_dept4，指定部门位置不能为空，SQL 语句和运行结果如下所示。

mysql> ALTER TABLE tb_dept4
    -> CHANGE COLUMN location
    -> location VARCHAR(50) NOT NULL;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> DESC tb_dept4;
+----------+-------------+------+-----+----------+-------+
| Field    | Type        | Null | Key | Default  | Extra |
+----------+-------------+------+-----+----------+-------+
| id       | int(11)     | NO   | PRI | NULL     |       |
| name     | varchar(22) | NO   |     | NULL     |       |
| location | varchar(50) | NO   |     | NULL     |       |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)

推荐教程：mysql视频教程

The above is the detailed content of How to set mysql primary key non-null constraint?. For more information, please follow other related articles on the PHP Chinese website!