How to set mysql primary key non-null constraint 04/02 Update SLTechnology News&Howtos

How to set mysql primary key non-null constraint

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

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

This article mainly introduces how to set the non-null constraint of mysql primary key, which can be used for reference by friends who need it. I hope you will learn a lot after reading this article. Next, let the editor take you to learn about it.

In mysql, you can set the primary key constraint by using the "PRIMARY KEY [default]" statement in the "CREATE TABLE" statement and the non-empty constraint by using the "NOT NULL" statement.

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.

When using primary keys, you should pay attention to the following points:

Only one primary key can be defined per table.

The primary key value must uniquely identify each row in the table and cannot be NULL, that is, there can be no 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 federated primary key field table.

The federated primary key cannot contain unnecessary extra fields. When a field of the federated primary key is deleted, the federated primary key is incorrect if the primary key formed by the remaining fields still satisfies the principle of uniqueness. This is the principle of minimization.

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.

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) 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.

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 ()

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

Example 4

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 non-null constraint

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

For example, in the user information table, if the user name is not added, then the user information is invalid, and a non-empty constraint can be set for the user name field.

Set a non-empty constraint when creating a table

When you create a table, you can use the NOT NULL keyword to set a non-null constraint. The syntax format is as follows:

NOT NULL

Example 1

Create the data table tb_dept4, and the specified department name cannot be empty. The SQL statement and run result are shown below.

Add a non-empty constraint when modifying a table

If you forget to set a non-empty constraint for the field when you create the table, you can also add the non-empty constraint by modifying the table.

The syntax format for setting non-null constraints when modifying the table is as follows:

ALTER TABLE CHANGE COLUMN NOT NULL

Example 2

Modify the data table tb_dept4 so that the location of the specified department cannot be empty. The SQL statement and run results are shown below.

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.