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 −

CREATETABLE table_name (
   column1 datatype NOTNULL,
   column2 datatype,
   column3 datatype NOTNULL,...);

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 −

CREATETABLE CUSTOMERS(
   ID INTNOTNULL,
   NAME VARCHAR(20)NOTNULL,
   AGE  INTNOTNULL,
   ADDRESS CHAR(25),
   SALARY DECIMAL(20,2),PRIMARYKEY(ID));

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

INSERTINTO 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 −

IDNAMEAGEADDRESSSALARY
1Ramesh32Ahmedabad2000.00
2Khilan25Delhi1500.00
3Kaushik23Kota2000.00
4Chaitali25Mumbai6500.00
5Hardik27Bhopal8500.00
6Komal22Hyderabad4500.00
7Muffy24Indore10000.00

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.

FieldTypeNullKeyDefaultExtra
IDintNOPRINULL
NAMEvarchar(20)NONULL
AGEintNONULL
ADDRESSchar(25)YESNULL
SALARYdecimal(20,2)YESNULL

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 −

ALTERTABLE table_name
MODIFYCOLUMN 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 −

ALTERTABLE CUSTOMERS MODIFYCOLUMN 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.

FieldTypeNullKeyDefaultExtra
IDintNOPRINULL
NAMEvarchar(20)YESNULL
AGEintNONULL
ADDRESSchar(25)YESNULL
SALARYdecimal(20,2)YESNULL

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 −

ALTERTABLE table_name
MODIFYCOLUMN column_name datatype NOTNULL;

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 −

ALTERTABLE CUSTOMERS MODIFYCOLUMN ADDRESS CHAR(25)NOTNULL;

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.

FieldTypeNullKeyDefaultExtra
IDintNOPRINULL
NAMEvarchar(20)NONULL
AGEintNONULL
ADDRESSchar(25)NONULL
SALARYdecimal(20,2)YESNULL

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *