MySQL not null constraint case explanation

The MySQL NOT NULL constraint means that the value of a field cannot be empty. 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 done through the 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 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 is invalid. At this time, you can set a non-null constraint for the username field.

Set a not null constraint when creating a table

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

<field name> <data type> NOT NULL;

Example 1

Create a data table tb_dept4 and specify that the 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)

Adding a not null constraint when modifying a table

If you forget to set a not-null constraint for a field when creating a table, you can also add a not-null constraint by modifying the table.

The syntax format for setting a non-empty constraint when modifying a table is as follows:

ALTER TABLE <table name>
CHANGE COLUMN <field name>
<field name> <data type> NOT NULL;

Example 2

Modify the data table tb_dept4 and specify that the department location cannot be empty. The SQL statement and running results are as follows.

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)

Remove Not Null Constraint

The syntax rules for deleting a non-null constraint when modifying a table are as follows:

ALTER TABLE <table name>
CHANGE COLUMN <field name> <field name> <data type> NULL;

Example 3

Modify the data table tb_dept4 and delete the non-null constraint of the department location. The SQL statement and running results are as follows.

mysql> ALTER TABLE tb_dept4
    -> CHANGE COLUMN location
    -> location VARCHAR(50) 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) | YES | | NULL | |
+----------+-------------+------+-----+----------+-------+
3 rows in set (0.00 sec)

This is the end of this article about the case study of MySQL not null constraint. For more relevant MySQL not null constraint content, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!