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)

Samual Sam

Learning faster. Every day.

Updated on: 19-Jun-2020

286 Views

Kickstart Your Career

Get certified by completing the course

Get Started