What is MySQL NOT NULL constraint and how can we declare a field NOT NULL while creating a table?

Actually, MySQL NOT NULL constraint restricts a column of the table from having a NULL value. Once we applied NOT NULL constraint to a column, then we cannot pass a null value to that column. It cannot be declared on the whole table i.e., in other words, we can say that NOT NULL is a column level constraint.

For declaring a field NOT NULL, we have to use NOT NULL keyword while defining the column in CREATE TABLE statement.

Example

mysql> Create table Employee(ID Int NOT NULL, First_Name Varchar(20), Last_name Varchar(20), Designation Varchar(15)); Query OK, 0 rows affected (0.59 sec)

In the query above, we have applied NOT NULL constraint on the field ‘ID’ of ‘Employee’ table. Now, the column ‘ID’ cannot take NULL value. It can be also checked from DESCRIBE statement that ID filed cannot accept NULL values.

mysql> DESCRIBE Employee123\G *************************** 1. row ***************************   Field: ID    Type: int(11)    Null: NO     Key: Default: NULL   Extra: *************************** 2. row ***************************   Field: First_Name    Type: varchar(20)    Null: YES     Key: Default: NULL   Extra: *************************** 3. row ***************************   Field: Last_name    Type: varchar(20)    Null: YES     Key: Default: NULL   Extra: *************************** 4. row ***************************   Field: Designation    Type: varchar(15)    Null: YES     Key: Default: NULL   Extra: 4 rows in set (0.03 sec)