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.

NOT NULL in SQL

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. While inserting data we must provide value of the respective column else the operation will fail and an error message will be displayed.

Syntax

The following is the basic syntax of NOT NULL 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 −

SQL>  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 query −

SQL>  INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(1,  Ramesh ,  32 ,  Ahmedabad , 2000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(2,  Khilan ,  25 ,  Delhi , 1500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(3,  kaushik ,  23 ,  Kota , 2500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(4,  Chaitap ,  25 ,  Mumbai , 6500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(5,  Hardik , 27 ,  Bhopal , 8500);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(6,  Komal ,  22 ,  MP , 9000);
INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(7,  Muffy ,  24 ,  Indore , 5500);

The table will be created as shown below −

+-----+-----------+-------+--------------+------------+
| ID  | NAME      | AGE   | ADDRESS      | SALARY     |
+-----+-----------+-------+--------------+------------+
|1    | Ramesh    | 32    | Ahmedabad    | 2000.00    |
|2    | Khilan    | 25    | Delhi        | 1500.00    |
|3    | kaushik   | 23    | Kota         | 2500.00    |
|4    | Chaitap  | 25    | Mumbai       | 6500.00    |
|5    | Hardik    | 27    | Bhopal       | 8500.00    |
|6    | Komal     | 22    | MP           | 9000.00    |
|7    | Muffy     | 24    | Indore       | 5500.00    |
+-----+-----------+-------+--------------+------------+

Verification

There is no specific query to display the structure of a table in SQL. To do so, we use the“sp_help” stored procedure in SQL Server to return the information about a specified object, including columns, constraints, and indexes.

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

SQL> EXEC sp_help  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.

Note − The above query displays lots of tables that contain detailed information about the “CUSTOMERS” table. From that, we are displaying only a single table that has information about the NOT NULL constraint.

+-------------+----------+----------+
| Column_name | Type     | Nullable |
+-------------+----------+----------+
| ID          | int      | no       |
| NAME        | varchar  | no       |
| AGE         | int      | no       |
| ADDRESS     | char     | yes      |
| SALARY      | decimal  | yes      |
+-------------+----------+----------+

Deleting a NOT NULL constraint from the table

In SQL, to delete 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 of ALTER TABLE statement in SQL −

ALTER TABLE table_name
ALTER COLUMN column_name datatype NULL;

Here,

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 query is trying to modify the constraint on the NAME column of the CUSTOMERS table created above,to NULL −

SQL> ALTER TABLE CUSTOMERS ALTER COLUMN NAME VARCHAR(20) NULL;

Output

On executing the given program, the output is displayed as follows −

Commands completed successfully.

Verification

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

SQL> EXEC sp_help  CUSTOMERS ;

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

+-------------+----------+----------+
| Column_name | Type     | Nullable |
+-------------+----------+----------+
| ID          | int      | no       |
| NAME        | varchar  | yes      |
| AGE         | int      | no       |
| ADDRESS     | char     | yes      |
| SALARY      | decimal  | yes      |
+-------------+----------+----------+

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.

In the same way using the ALTER TABLE statement we can add a NOT NULL constraint to a column in an existing table.

Syntax

Following is the syntax of ALTER TABLE statement in SQL to add the NOT NULL constraint to the existing column −

ALTER TABLE table_name
ALTER COLUMN column_name datatype NOT NULL;

Example

Assume the previously created table CUSTOMERS and let us try to modify the ADDRESS column to NOT allow NULL values using the following query −

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

Output

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

Commands completed successfully.

Verification

If you display the structure of the CUSTOMERS table by calpng the sp_help procedure, you can observe that the NULL constraint on the address column is removed (modified to NULL) −

SQL> EXEC sp_help  CUSTOMERS ;

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

+-------------+----------+----------+
| Column_name | Type     | Nullable |
+-------------+----------+----------+
| ID          | int      | no       |
| NAME        | varchar  | yes      |
| AGE         | int      | no       |
| ADDRESS     | char     | no       |
| SALARY      | decimal  | yes      |
+-------------+----------+----------+