Objective and Introduction
Objective: This segment introduces how to define, add, and remove the NOT NULL constraint to a column.
Introduction: The NOT NULL constraint used to ensure that the values stored in a column are NOT NULL. The following example illustrate, how to define, add, and remove the NOT NULL constraint.
Defining NOT NULL constraint
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| DB |
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> USE DB;
Database changed
mysql> CREATE TABLE holders(
-> account_no BIGINT PRIMARY KEY,
-> name VARCHAR(30) NOT NULL,
-> city VARCHAR(20),
-> dob DATE,
-> bank VARCHAR(15),
-> amount BIGINT NOT NULL);
Query OK, 0 rows affected (0.52 sec)
mysql> DESCRIBE holders;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| account_no | bigint(20) | NO | PRI | NULL | |
| name | varchar(30) | NO | | NULL | |
| city | varchar(20) | YES | | NULL | |
| dob | date | YES | | NULL | |
| bank | varchar(10) | YES | | NULL | |
| amount | bigint(20) | NO | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
Adding NOT NULL constraint to a column
mysql> ALTER TABLE holders MODIFY dob DATE NOT NULL; Query OK, 0 rows affected (2.77 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC holders; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | account_no | bigint(20) | NO | PRI | NULL | | | name | varchar(30) | NO | | NULL | | | city | varchar(20) | YES | | NULL | | | dob | date | NO | | NULL | | | bank | varchar(10) | YES | | NULL | | | amount | bigint(20) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Retrieving records from a table
If you observe the records, the last record's city is NULL. Hence, while adding the NOT NULL constraint to a city column, got ERROR 1138 (22004): Invalid use of NULL value. To fix this issue, we need to update the last record's city in the holders table.
mysql> ALTER TABLE holders MODIFY city VARCHAR(20) NOT NULL;ERROR 1138 (22004): Invalid use of NULL value mysql> SELECT * FROM holders; +-------------+---------------+---------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+---------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348992 | Ryan Bakshi | Mumbai | 1982-01-14 | Citi | 50000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | NULL | 1978-06-26 | Citi | 97800 | +-------------+---------------+---------+------------+----------+--------+ 7 rows in set (0.00 sec)
Updating a record and fixing ERROR 1138 (22004): Invalid use of NULL value
mysql> UPDATE holders SET city='New York' where account_no=25622348995; Query OK, 1 row affected (0.15 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM holders; +-------------+---------------+----------+------------+----------+--------+ | account_no | name | city | dob | bank | amount | +-------------+---------------+----------+------------+----------+--------+ | 25622348989 | James Moore | Phoenix | 1985-05-26 | Barclays | 5000 | | 25622348990 | Donald Taylor | Irvine | 1990-08-20 | Citi | 7000 | | 25622348991 | Edward Parkar | Irvine | 1994-01-29 | ICICI | 95000 | | 25622348992 | Ryan Bakshi | Mumbai | 1982-01-14 | Citi | 50000 | | 25622348993 | Marie Peters | Ribe | 1967-01-05 | DZBank | 12250 | | 25622348994 | Aanya | Delhi | 1975-08-18 | SBI | 105000 | | 25622348995 | James Moore | New York | 1978-06-26 | Citi | 97800 | +-------------+---------------+----------+------------+----------+--------+ 7 rows in set (0.00 sec) mysql> ALTER TABLE holders MODIFY city VARCHAR(20) NOT NULL; Query OK, 0 rows affected (1.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC holders; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | account_no | bigint(20) | NO | PRI | NULL | | | name | varchar(30) | NO | | NULL | | | city | varchar(20) | NO | | NULL | | | dob | date | NO | | NULL | | | bank | varchar(10) | YES | | NULL | | | amount | bigint(20) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.00 sec)
Removing NOT NULL constraint
mysql> ALTER TABLE holders MODIFY city VARCHAR(20) NULL; Query OK, 0 rows affected (1.48 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> DESC holders; +------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+-------+ | account_no | bigint(20) | NO | PRI | NULL | | | name | varchar(30) | NO | | NULL | | | city | varchar(20) | YES | | NULL | | | dob | date | NO | | NULL | | | bank | varchar(10) | YES | | NULL | | | amount | bigint(20) | NO | | NULL | | +------------+-------------+------+-----+---------+-------+ 6 rows in set (0.01 sec)
What Next?
MySQL ALTER TABLE (ADD, DROP, MODIFY, CHANGE, RENAME TO)
MySQL DROP a TABLE and DATABASE
MySQL Data Manipulation
Advertisement