Summary: in this tutorial, you will learn about PostgreSQL not-null constraint to ensure that the value of a column is not null.
In database theory, NULL is unknown or missing information. The NULL value is different from empty or zero. For example, we can ask for the email address of a person, if we don’t know, we use the NULL value. In case the person does not have any email address, we can mark it as an empty string.
The NULL value is very special. For example, NULL is not equal to anything even NULL. To check if a value is NULL or not, you use the Boolean operator IS NULL
or IS NOT NULL
. The expression NULL = NULL
returns NULL.
PostgreSQL provides the not-null constraint to enforce a column must not accept NULL values. It means that whenever you insert or update data, you must specify a value that is different from the NULL value.
Add PostgreSQL not-null constraint to columns when creating new table
The following CREATE TABLE statement creates a new table name invoice
with not-null constraint.
1 2 3 4 5 6 | CREATE TABLE invoice( id serial PRIMARY KEY, product_id int NOT NULL, qty numeric NOT NULL CHECK(qty > 0), net_price numeric CHECK(net_price > 0) ); |
We used NOT NULL
followed by the data type of the column to declare the not-null constraint. In this case, it is a column-constraint.
Note that a column can have multiple constraints such as the not-null, check, unique, foreign key appeared next to each other. The order is not important and we are not sure PostgreSQL will check which constraint first. If you use NULL instead of NOT NULL, the column will accept both null and non-null values. If you don’t use both NOT NULL and NULL, PostgreSQL will use NULL by default.
Add PostgreSQL not-null constraint to columns of existing table
To add not-null constraint to a column of an existing table, we use ALTER TABLE statement as follows:
1 2 | ALTER TABLE table_name ALTER COLUMN column_name SET NOT NULL; |
To add not-null constraints to multiple columns of an existing table, we use the following syntax:
1 2 3 | ALTER TABLE table_name ALTER COLUMN column_name_1 SET NOT NULL, ALTER COLUMN column_name_2 SET NOT NULL; |
Let’s take a look at the following example.
First, we create a new table named production orders ( production_orders
):
1 2 3 4 5 6 7 8 | CREATE TABLE production_orders ( ID serial PRIMARY KEY, description VARCHAR (40) NOT NULL, material_id VARCHAR (16), qty NUMERIC, start_date DATE, finish_date DATE ); |
Next, we insert a new row into the production_orders
table:
1 2 3 | INSERT INTO production_orders (description) VALUES ('make for infosys inc.'); |
Then, we want to make sure that the qty
field is not null so we will add the not-null constraint to the qty
column. However, the table already has data in it. If we try to add the not-null constraint, PostgreSQL will issue an error message.
So we need to update the data first before adding the not-null constraint.
1 2 | UPDATE production_orders SET qty = 1; |
We updated the qty column value to 1.
1 2 | ALTER TABLE production_orders ALTER COLUMN qty SET NOT NULL; |
After that, we can update the not-null constraints for material_id
, start_date
, and finish_date
columns:
1 2 3 4 | UPDATE production_orders SET material_id = 'ABC', start_date = '2015-09-01', finish_date = '2015-09-01'; |
Add not-null constraints to multiple columns:
1 2 3 4 | ALTER TABLE production_orders ALTER COLUMN material_id SET NOT NULL, ALTER COLUMN start_date SET NOT NULL, ALTER COLUMN finish_date SET NOT NULL; |
Finally, let’s try to break the not-null constraint:
1 2 | UPDATE production_orders SET qty = NULL; |
PostgreSQL issued an error message:
1 2 | [Err] ERROR: null value in column "qty" violates not-null constraint DETAIL: Failing row contains (1, make for infosys inc., ABC, null, 2015-09-01, 2015-09-01). |
The Not-null constraint special case
You can use the check constraint to represent the not-null constraint. So the
1 | NOT NULL |
is equivalent to
1 | CHECK(column IS NOT NULL) |
This is useful because sometimes you may want either column a or b is not null, but not both. For example, in the users
table, you want either username
or email
column is not null. You can use the check constraint as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | CREATE TABLE users ( ID serial PRIMARY KEY, username VARCHAR (50), PASSWORD VARCHAR (50), email VARCHAR (50), CONSTRAINT username_email_notnull CHECK ( ( username IS NOT NULL AND email IS NULL ) OR ( email IS NULL AND username IS NOT NULL ) ) ); |
In this tutorial, we have introduced you to NULL concept and how to use PostgreSQL not-null constraint to make sure that the values in columns are not NULL.