What Is a NOT NULL Constraint?
The NOT NULL constraint ensures that a column cannot contain null values which require every row in the table to have a non-null value for that particular column.
Tutorial Requirements:
To follow along with the instructions that are provided in this post, we assume that you have the following requirements:
- Installed PostgreSQL server on your machine
- Basic SQL knowledge
- Permission to create the database table objects such as constraints, functions, etc
Example Table:
Let us start by setting up a sample table with columns that contain a not null constraint. Take the following create table statement that sets up a basic table that resembles the wp_users table in WordPress:
ID SERIAL PRIMARY KEY,
user_login VARCHAR(60) NOT NULL,
user_pass VARCHAR(255) NOT NULL,
user_nicename VARCHAR(50) NOT NULL,
user_email VARCHAR(100) NOT NULL,
user_url VARCHAR(100),
user_registered TIMESTAMP NOT NULL DEFAULT current_timestamp,
user_activation_key VARCHAR(255),
user_status INT NOT NULL DEFAULT 0,
display_name VARCHAR(250) NOT NULL
);
From the given query, you will notice that most columns contain a NOT NULL constraint.
The most efficient method of creating a NOT NULL constraint in a given column is during the table creation. This ensures that all the data that are added to the table adheres to the rules of the constraint.
However, if you add a constraint to an existing table, the rules only apply to the data that is added after the constraint and not to any existing data. This can lead to inconsistencies, especially in large tables.
Add a NOT NULL Constraint
In some cases, you may want to add a NOT NULL constraint to an existing table. For example, you can use the ALTER TABLE command followed by the ALTER COLUMN clause.
The syntax is as follows:
ALTER COLUMN column_name SET NOT NULL;
Replace the table_name with your target table and the column_name with the target column to which you want to apply the constraint.
Verify the Constraint
You can verify that the constraint exists on a given table using the “\d” command in the PSQL utility.
Example:
The resulting output is as follows:
Remove the NOT NULL Constraint
You may also want to remove the NOT NULL constraint from a given column. For that, we can use the ALTER TABLE and ALTER COLUMN clauses as follows:
ALTER COLUMN column_name DROP NOT NULL;
For example, to remove the NOT NULL constraint from the user_url column of the wp_users table, we can run the following query:
This should drop the constraint from the table.
Conclusion
This tutorial explored on how we can work with the NOT NOLL constraint in PostgreSQL. We also explored how to drop an existing NOT NULL constraint from a given column.
