A2-03-08.DDL-MySQL NOT NULL Constraint
转载自：http://www.mysqltutorial.org/mysql-not-null-constraint/
MySQL NOT NULL Constraint
Summary: this tutorial introduces you to the MySQL NOT NULL constraint that helps you keep your data consistent.
Introduction to MySQL NOT NULL constraint
The NOT NULL constraint is a column constraint that forces the values of a column to non-NULL values only.
The syntax of the NOT NULL constraint is as follows:
|
1
|
column_name data_type NOT NULL;
|
A column may contain one NOT NULL constraint only, which specifies a rule that the column must not contain any NULL value.
The following CREATE TABLE statement creates the tasks table:
|
1
2
3
4
5
6
|
CREATE TABLE tasks (
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(255) NOT NULL,
start_date DATE NOT NULL,
end_date DATE
);
|
The title and start_date columns have the NOT NULL constraints explicitly. The id column has the PRIMARY KEY constraint, therefore, it implicitly includes a NOT NULL constraint.
The end_date column can have NULL values. This is because when we add a new task we may not know its end date.
It’s a best practice to have the NOT NULL constraint in every column of a table unless you have a good reason not to do so.
Generally, the NULL value makes your queries more complicated. In such cases, you can use the NOT NULL constraint and provide a default value for the column. See the following example:
|
1
2
3
4
5
6
7
|
CREATE TABLE inventory (
material_no VARCHAR(18),
warehouse_no VARCHAR(10),
quantity DECIMAL(19 , 2 ) NOT NULL DEFAULT 0,
base_unit VARCHAR(10) NOT NULL,
PRIMARY KEY (material_no , warehouse_no)
);
|
In this example, the default value for quantity column is 0. Because at the time we add a row to the inventory table, the value of the quantity column should be 0, not NULL.
Add a NOT NULL constraint to an existing column
Typically, you add a NOT NULL constraints to columns when you create the table. However, sometimes, you want to add a NOT NULL constraint to NULL-able column of an existing table. In this case, you use the following steps:
- Check the current values of the column.
- Update the
NULLvalues to non-null values. - Add the NOT
NULLconstraint
Let’s take a look at an example.
We insert data into the tasks table for the demonstration.
|
1
2
3
|
INSERT INTO tasks(title ,start_date, end_date)
VALUES('Learn MySQL NOT NULL constraint', '2017-02-01','2017-02-02'),
('Check and update NOT NULL constraint to your database', '2017-02-01',NULL);
|
Now, suppose you want to force users to give estimated end date when creating a new task. To do this, you need to add the NOT NULL constraint to the end_date column of the tasks table.
First, check the value of end_date table. We use the IS NULL operator to check if the value in a column is NULL or not:
|
1
2
3
4
5
6
|
SELECT
*
FROM
tasks
WHERE
end_date IS NULL;
|
The query returns one row with the end_date value is NULL.
Second, update the NULL values to non-null values. In this case, we can create a rule that if the end_dateis NULL, we make the end date one week after the start date.
|
1
2
3
4
5
|
UPDATE tasks
SET
end_date = start_date + 7
WHERE
end_date IS NULL;
|
Let’s check the change:
|
1
2
3
4
|
SELECT
*
FROM
tasks
|
Third, add the NOT NULL constraint to the code end_date column. To do it, you use the following ALTER TABLE statement:
|
1
2
|
ALTER TABLE table_name
CHANGE old_column_name new_column_name new_column_definition;
|
In our case, the old column name and the new column name must be the same except for the column definition that has the NOT NULL constraint:
|
1
2
|
ALTER TABLE tasks
CHANGE end_date end_date DATE NOT NULL;
|
Let’s verify the change by using the DESCRIBE statement:
|
1
|
DESCRIBE tasks;
|
As you see, the NOT NULL constraint was added to the end_date column.
In this tutorial, you have learned how to define NOT NULL constraint for a column and add the NOT NULLconstraint to an existing column.
