SQL - NOT NULL Constraint

---

---

The SQL NOT NULL Constraint

The NOT NULL constraint in SQL ensures that a column cannot contain NULL (empty) values. It inserting or updating a row with NULL in that column.

By default, if no value is provided for a column when inserting data, SQL assigns it a NULL value. However, when the NOT NULL constraint is applied, a value must be provided for that column during insertion. If a NULL value is attempted, SQL will return a constraint violation error.

SQL NOT NULL on CREATE TABLE

When defining a table, you can use the NOT NULL constraint in the CREATE TABLE statement to ensure that specific columns cannot store NULL values. This guarantees that each row will always have a valid value for those columns.

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,
   ...
);

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.

SQL NOT NULL on ALTER Table

You can add or remove the NOT NULL constraint on an existing table using the ALTER TABLE statement. Adding this constraint ensures that a column cannot store NULL values, while removing it allows NULL values in that column.

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;

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;

Where,

• 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: Add NOT NULL constraint

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;

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.

Example: Remove NOT NULL constraint

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;

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.

Advantages of SQL NOT NULL Constraint

The NOT NULL constraint helps to keep a database accurate by making sure certain fields are never left empty, and it also makes it easier to follow the rules set for the data:

• It makes sure that important fields in a table are never left empty, so every record has the required information.
• It helps to keep data accurate and meaningful by not allowing missing values in key columns.
• It makes queries more consistent because you don't have to deal with unexpected empty values in important fields.
• It lets you set rules in the database so certain fields must always have data, without depending only on checks in the application.
• It can make searches and indexing faster in some databases because fields marked as NOT NULL may be stored and processed more effectively.