Constraints in PostgreSQL
Constraints are rules for the values in a column. PostgreSQL checks these rules every time you insert or update a row. If a row breaks a rule, PostgreSQL stops with an error and does not save the row. This keeps your data correct.
The common constraints are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY and CHECK. You can add them when you create a table, or later with ALTER TABLE.
NOT NULL Constraint
The NOT NULL constraint makes sure that a column always has a value. The column can never be NULL.
Syntax
CREATE TABLE table_name (
column_name datatype NOT NULL
);
Or for a table that already exists:
ALTER TABLE table_name
ALTER COLUMN column_name SET NOT NULL;
Example
Let's create a students table where the name column must not be NULL:
CREATE TABLE students (
student_id INT,
name VARCHAR(50) NOT NULL,
age INT
);
Output:
CREATE TABLE
Now let's try to insert a student without a name:
INSERT INTO students (student_id, age)
VALUES (1, 20);
Output:
ERROR: null value in column "name" of relation "students" violates not-null constraint
DETAIL: Failing row contains (1, null, 20).
PostgreSQL refused the row, because name is required. The DETAIL line shows the row that failed.
When to Use NOT NULL?
- When a column must always have a value.
- For important fields like names, email addresses and ids.
Next, you will learn the UNIQUE constraint, which stops duplicate values in a column.
Answer the questions below to check what you learned about constraints.