PostgreSQL Not-Null Constraint

In this section, we are going to understand the working of the PostgreSQL NOT NULL constraint, which is used to make sure that the values of a column are not null.

Examples of the PostgreSQL NOT NULL constraint, how to declare the PostgreSQL NOT NULL constraint, and adding NOT NULL Constraint to existing columns.

Before understanding the concept of PostgreSQL NOT NULL constraint, we are going to learn about the Null.

What is NULL?

The NULL is used to signifies the unknown or information missing. And it is not similar to an empty string or the number zero in the database theory.

For example, if we want to insert the customer’s mobile number into a particular table, we can request his/her mobile number. But if we don’t know whether the customer has a mobile number or not, we can insert NULL into the mobile number column.

In such a case, the NULL specifies that the mobile number is unknown at the time of recording.

The NULL is very important as it does not equivalent to anything, even itself, as we can observe in the below expression:

NULL = NULL   

The above statement will return NULL as it makes sense that the two unidentified values should not be equal.

We can use the IS NULL Boolean operator if we want to check that the value is NULL or not. For example, the below statement returns true if the mobile number column’s value is NULL.

Mobile_number IS NULL  

Note:

• The IS NOT NULLoperator contradicts the output of the IS NULL
• We should never use (=) equal operatorfor comparing the value with NULL as it always returns NULL.

What is PostgreSQL Not Null Constraint?

In PostgreSQL, the not-null constraint is a column, which can hold the Null values by default. If we don’t want a column to have a NULL value, we need to explain such constraint on this column state, and NULL is now not acceptable for that particular column.

The not null constraint is always created as a column constraint, and it represents unknown data, but it doesn’t mean that the data should be null.

In other words, we can say that the PostgreSQL not-null constraint is used to make sure that a column cannot have any null value.

And it is a column constraint which cannot be used as a table constraint that means no name can be specified to create a not-null constraint. We can place the not-null constraint directly after the datatype of a column.

The Syntax for PostgreSQL not-null constraint

In the below illustration, we will use the NOT NULL constraint to check either a column can accept NULL values or not:

CREATE TABLE table_name(  

   ...  

 column_name data_type NOT NULL,  

   ...  

);

Note: If a column contains a NOT NULL constraint, or we are trying to insert or update NULL in the column, it will issue an error in the output.

Example of PostgreSQL NOT NULL Constraint

To understand the working of the PostgreSQL not null constraint, we will see the below example, which describes how the NOT NULL constraint is used in PostgreSQL.

In the below command, we will create one new table as Bills with the CREATE command’s help and inserting some values using the INSERT command.

To create the Bills into a Javatpoint database, we will use the CREATE command containing the various columns such as Bill_id, Item_id, Quantity, Actual_price column.

CREATE TABLE Bills(  

 Bill_id SERIAL PRIMARY KEY,  

  Item_id INT NOT NULL,  

  Quantity NUMERIC NOT NULL CHECK(Quantity > 0),  

  Actual_price NUMERIC CHECK(Actual_price > 0)   

);

Output

On executing the above command, we will get the following message: The Bills table has been created successfully into the Jtp database.

In the above example, we have used the NOT NULL keywords after the data type of the Item_id and Quantity columns to represent the NOT NULL constraints.

Note:

• In PostgreSQL, a column can contain various constraints such as CHECK, FOREIGN KEY, UNIQUE KEY, and NOT NULL, which appeared next to each other.
• PostgreSQL can control the constraint list in any sequence because the sequence of the constraints is not important.
• By default, it accepts the NULL value if we do not define the NOT NULL or NULL

Adding a PostgreSQL NOT NULL Constraint to existing columns using an ALTER TABLE command

In PostgreSQL, we can add the NOT NULL Constraint to a column of an existing table with the ALTER TABLE command’s help.

The syntax for adding the not null constraint using an ALTER table command

The below illustrations of the ALTER TABLE command are used to add a NOT NULL constraint into the existing table:

ALTER TABLE table_name  

ALTER COLUMN column_name   

SET NOT NULL;

OR

We can use the below syntax if we want to add several NOT NULL constraints to various columns:

ALTER TABLE table_name  

ALTER COLUMN column_name_1 SET NOT NULL,  

ALTER COLUMN column_name_2 SET NOT NULL,  

...;

Example of PostgreSQL Not Null constraint using ALTER TABLE command

To understand the PostgreSQL NOT NULL constraint’s working, we will see the below example, which describes how a NOT NULL constraint is added through the ALTER table command in PostgreSQL.

In the below example, we are going to create a new table called Orders into the Jtp database.

Before creating the Orders table, we will use the DROP TABLE command if a similar table exists in the Jtp database.

DROP TABLE IF EXISTS Orders;  

Output

After executing the above command, we will get the following window message, which displays that the Orders table has been dropped successfully.

Once the previously created Orders table has been dropped, we will create a new Orders table, which contains the various columns such as ID, Specification, Raw_material_id, Quantity, Begin_id, and End_id column.

CREATE TABLE Orders (  

    ID SERIAL PRIMARY KEY,  

    Specification VARCHAR (50) NOT NULL,  

    Raw_material_id VARCHAR (15),  

    Quantity NUMERIC,  

    Begin_date DATE,  

    End_date DATE  

);

Output

After implementing the above command, we will get the following message: The Orders table has been created successfully into the Jtp database.

Once the Orders table has been created successfully, we will insert few records into it with the INSERT command’s help, as shown in the following command:

INSERT INTO Orders (Specification)  

VALUES('Make for TCS');

Output

On executing the above command, we will get the following message window, which displays that the one value has been inserted successfully into the Orders table.

After that, we can add the not-null constraint to the Quantity column to ensure that the Quantity column is not null although the column already contains some data.

To add the NOT NULL constraint into a column which containing the NULL values, we will follow the below steps.

Step1

Firstly, we need to update the NULL to NOT-NULL, as shown in the following statement:

UPDATE Orders  

SET Quantity = 1;

Output

After executing the above command, we will get the below message window, which displays that the Orders table has been updated successfully.

As we can see in the above screenshot that the values in the Quantity column are updated to one.

Step2

Now, we will add the NOT NULL constraint to the Quantity column using the ALTER table command, as we can see in the following statement:

ALTER TABLE Orders   

ALTER COLUMN Quantity  

SET NOT NULL;

Output

On implementing the above command, we will get the following message window, which shows that the Orders table has been modified successfully.

Step3

After that, we will update the not-null constraints for Raw_material_id, Begin_date, and End_date columns with the help of the below command:

UPDATE Orders  

SET Raw_material_id = 'PQR',  

 Begin_date = '2013-04-08',  

 End_date = '2013-04-08';

Output

We will get the below message window, which displays that the Orders table has been updated successfully after executing the above command:

Step4

After that, we will be adding the Not-Null constraints to the various columns, as shown in the below statement:

ALTER TABLE Orders   

ALTER COLUMN Raw_material_id SET NOT NULL,  

ALTER COLUMN Begin_date SET NOT NULL,  

ALTER COLUMN End_date SET NOT NULL;

Output

On executing the above command, we will retrieve the following message window, which shows that the particular table has been altered successfully.

Step5

Lastly, we will try to modify the values in the Quantity column to NULL, as we can observe the following statement:

UPDATE Orders  

SET Quantity = NULL; 

Output

After implementing the above command, PostgreSQL issued the below error message:

ERROR: null value in column “quantity” violates not-null constraint DETAIL: Failing row contains (1, Make for TCS, PQR, null, 2013-04-08, 2013-04-08).

The different case of NOT NULL Constraint

In addition to PostgreSQL Not Null Constraint, we can use a CHECK constraint to force a column for accepting the accept NULL values, and the NOT NULL constraint is equal to the below CHECK constraint:

CHECK(column IS NOT NULL)  

Let us see one sample example for understanding the special case of PostgreSQL Not Null Constraint.

Suppose we want both Login_name or Phone_number column of the Buyer table either not Null or empty. In such a case, we can use the CHECK constraint.

For this, firstly, we will create the Buyer table with the help of the Create Table command’s help, as shown in the following command:

CREATE TABLE Buyer (  

ID serial PRIMARY KEY,  

Login_name VARCHAR (40),  

Password VARCHAR (40),  

Phone_number VARCHAR (40),  

CONSTRAINT Login_name_Phone_number_notnull CHECK (  

   NOT (  

     (Login_name IS NULL OR  Login_name = '' )  

     AND  

     ( Phone_number IS NULL  OR  Phone_number = '' )  

   )  

 )  

);

Output

After implementing the above command, we will get the below message window, which displays that the Buyer table has been created successfully into the jtp database.

After creating the new table as Buyer successfully, we will enter some values into it with the INSERT command’s help.

INSERT INTO Buyer (Login_name, Phone_number)  

VALUES  

    ('Login1', NULL),  

    (NULL, '9876543210'),  

    ('Login2,'0123456789'),  

    ('Login3', '');

Output

After implementing the above command, we will get the following message window, which displays that the four values have been inserted successfully into the Buyer table.

But the below command will not work as it interrupts the CHECK constraint:

INSERT INTO Buyer (Login_name, Phone_number)  

VALUES   

(NULL, ''),  

(NULL, NULL),   

('', ''),  

('', NULL);

Output

After executing the above command, PostgreSQL will raise the following error:

ERROR: new row for relation “buyer” violates check constraint “login_name_phone_number_notnull” DETAIL: Failing row contains (5, null, null,).

Overview

In the PostgreSQL Not Null Constraint section, we have learned the following topics:

• We have used the NOT NULL constraint to make sure that the values of a column are not null.
• We have understood the different cases of PostgreSQL NOT NULL constraint.
• To check if a value is NULLor not, we have used the IS NULL operator, where the IS NOT NULL denies the output of the IS NULL.