SQL - NOT NULL Constraint

---

---

In a table, columns can typically accept NULL values by default. However, if you want to ensure that a particular column does not contain NULL values, you need to add the NOT NULL constraint/condition on that column.

The SQL NOT NULL Constraint

The NOT NULL constraint in SQL is used to ensure that a column in a table doesn't contain NULL (empty) values, and prevent any attempts to insert or update rows with NULL values.

Usually, if we don't provide value to a particular column while inserting data into a table, by default it is considered as a NULL value. But, if we add the NOT NULL constraint on a column, it will enforce that a value must be provided for that column during the data insertion, and attempting to insert a NULL value will result in a constraint violation error.

Syntax

Following is the basic syntax of NOT NULL constraint while creating a table −

CREATE TABLE table_name (
   column1 datatype NOT NULL,
   column2 datatype,
   column3 datatype NOT NULL,
   ...
);

Creating NOT NULL Constraint On a Table

To add the NOT NULL constraint on a column of a table, we just need to add the keyword "NOT NULL" after the column's data type in the column definition.

Example

First of all, let us create a table named CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT             NOT NULL,
   NAME VARCHAR (20)  NOT NULL,
   AGE  INT           NOT NULL,
   ADDRESS CHAR (25) ,
   SALARY DECIMAL (20, 2),       
   PRIMARY KEY (ID)
);

Let's insert some values into the above created table using the following INSERT query −

INSERT INTO CUSTOMERS VALUES
(1, 'Ramesh', '32', 'Ahmedabad', 2000),
(2, 'Khilan', '25', 'Delhi', 1500),
(3, 'Kaushik', '23', 'Kota', 2500),
(4, 'Chaitali', '25', 'Mumbai', 6500),
(5, 'Hardik','27', 'Bhopal', 8500),
(6, 'Komal', '22', 'Hyderabad', 9000),
(7, 'Muffy', '24', 'Indore', 5500);

The table will be created as shown below −

Verification

To display the structure of a table in MySQL database, we use the DESCRIBE command. The DESCRIBE command provides a summary of the columns, data types, and various attributes of the table as shown below −

DESCRIBE CUSTOMERS;

As we can see in the output below, the table shows information about the column names of the table, their types, and whether they are nullable or not.

Removing a NOT NULL Constraint From the Table

In SQL, to remove a NOT NULL constraint of a column in an existing table, we need to use the ALTER TABLE statement. Using this statement, we can modify the definition of a column i,e you can change the name, data type or constraint of an existing column.

One of a way to remove the NOT NULL constraint on a column is to changing it to NULL.

Syntax

Following is the syntax to remove a not null constraint from the table in MySQL database −

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NULL;

Were,

• table_name is the name of the table that contains the columns we want to modify.
• column_name is the name of the column that has the NOT NULL constraint you want to remove.
• datatype is the data type of the column.

Example

Following is the query to modify the constraint on the NAME column of the CUSTOMERS table to NULL in MySQL database −

ALTER TABLE CUSTOMERS MODIFY COLUMN NAME VARCHAR(20) NULL;

Output

On executing the above query, the output is displayed as follows −

Query OK, 0 rows affected (0.10 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

Now, let us display the structure of the table named "CUSTOMERS" using the following query −

DESCRIBE CUSTOMERS;

As we can see in the table below, the column "NAME" is modified to nullable, which means NULL values are allowed in this column.

Adding a NOT NULL Constraint to the Existing Table

In the previous section, we have removed the NOT NULL constraint on a column by changing its definition using the ALTER TABLE statement. Similarly, we can add a NOT NULL constraint to a column in an existing table using the ALTER TABLE statement.

Syntax

Following is the SQL syntax to add the NOT NULL constraint to the existing column in MySQL database −

ALTER TABLE table_name
MODIFY COLUMN column_name datatype NOT NULL;

Example

Assume the previously created table CUSTOMERS and let us modify the ADDRESS column ensuring that it does not allow null values using the following query −

ALTER TABLE CUSTOMERS MODIFY COLUMN ADDRESS CHAR(25) NOT NULL;

Output

When we execute the above query, the output is obtained as follows −

Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

Verification

We can display the structure of the CUSTOMERS table using the following query −

DESCRIBE CUSTOMERS;

As we can see in the output below, the column "ADDRESS" is modified, which means NULL values are NOT allowed in this column.