SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values.
This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.
SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT accept NULL values when the "Persons" table is created:
Example
SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:
Ограничение NOT NULL в SQL
Ограничение NOT NULL означает, что столбец не может хранить значения NULL . Например:
Здесь столбцы college_id и college_code таблицы Colleges не могут иметь значения NULL .
Примечание: Ключевые слова NOT NULL используются для добавления ограничений, тогда как условия IS NULL и IS NOT NULL используются с оператором WHERE для выбора строк из таблицы.
Удалить ограничение NOT NULL
Мы также можем удалить ограничение NOT NULL для столбца, если оно больше не нужно. Например:
insert a NOT NULL column to an existing table
As an option you can initially create Null-able column, then update your table column with valid not null values and finally ALTER column to set NOT NULL constraint:
Another option is to specify correct default value for your column:
UPD: Please note that answer above contains GO which is a must when you run this code on Microsoft SQL server. If you want to perform the same operation on Oracle or MySQL you need to use semicolon ; like that:
MySQL NOT NULL Constraint
Summary: in this tutorial, you will learn how to define a NOT NULL constraint for a column, add a NOT NULL constraint to an existing column, and remove a NOT NULL constraint from a column.
Introduction to the MySQL NOT NULL constraint
The NOT NULL constraint is a column constraint that ensures values stored in a column are not NULL .
The syntax of defining a NOT NULL constraint is as follows:
A column may contain only one NOT NULL constraint which specifies a rule that the column must not contain any NULL value. In other words, if you update or insert NULL into a NOT NULL column, MySQL will issue an error.
The following CREATE TABLE statement creates the tasks table:
In the tasks table, we explicitly define the title and start_date columns with NOT NULL constraints. The id column has the PRIMARY KEY constraint, therefore, it implicitly includes a NOT NULL constraint.
The end_date column can have NULL values, assuming that when you create a new task, you may not know when the task can be completed.
It’s a good 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 because you have to use functions such as ISNULL() , IFNULL() , and NULLIF() for handling NULL .
Add a NOT NULL constraint to an existing column
Typically, you add NOT NULL constraints to columns when you create the table. Sometimes, you want to add a NOT NULL constraint to a NULL-able column of an existing table. In this case, you use the following steps:
- Check the current values of the column if there is any NULL .
- Update the NULL to non- NULL if NULLs exist.
- Modify the column with a NOT NULL constraint.
Consider the following example.
The following statement inserts some rows into the tasks table for the demonstration.
Suppose that you want to force users to give an estimated end date when creating a new task. To implement this rule, you add a NOT NULL constraint to the end_date column of the tasks table.
First, use the IS NULL operator to find rows with NULLs in the column end_date :
The query returned one row with NULL in the column end_date .
Second, update the NULL values to non-null values. In this case, you can make up a rule that if the end_date is NULL , the end date is one week after the start date.
This query verifies the update:
Third, add a NOT NULL constraint to the end_date column using the following ALTER TABLE statement:
In this case, the name of the old and new column names are the same except that the column must have a NOT NULL constraint:
Let’s verify the change by using the DESCRIBE statement:
As you see, the NOT NULL constraint was added to the end_date column successfully.
Drop a NOT NULL constraint
To drop a NOT NULL constraint for a column, you use the ALTER TABLE..MODIFY statement:
Note that the column definition (column_definition) must restate the original column definition without the NOT NULL constraint.
For example, the following statement removes the NOT NULL constraint from the end_date column in the tasks table:
To ensure that the statement actually removed the NOT NULL constraint, you can use the SHOW CREATE TABLE command to view the full column definition:
In this tutorial, you have learned how to define a NOT NULL constraint for a column, add a NOT NULL constraint to a column, and remove a NOT NULL constraint from a column.