Constraints are used to enforce rules on the data of columns. These are defined primarily to ensure correctness and validity of the data entering into the database.
Constraints could be at the column level or table level.
Constraints on Columns
Primary key is used to identify each row of a table uniquely.
CREATE TABLE author(id SERIAL PRIMARY KEY,name TEXT);
id column has been tagged as the primary key for this table. We will learn more about Primary Keys in the next section.
A NOT NULL constraint allows you to specify that the column cannot have a
null value. This is important because, sometimes having a null value in unexpected places could create inconsistencies in data.
CREATE TABLE author(id SERIAL PRIMARY KEY,name TEXT NOT NULL);
NOT NULL constraint has been applied to the
Note: If you had created the table
author for trying out the primary key constraint, make sure to either drop the table or change the table name for trying out further examples.
A CHECK constraint lets you specify a Boolean expression to validate data to be inserted.
CREATE TABLE author(id SERIAL PRIMARY KEY,name TEXT NOT NULL,rating INT NOT NULL CHECK(rating > 0 AND rating <= 10));
In the above example, the value of rating can be between 1 to 10 and that is enforced using the
Additionally, you can also set
DEFAULT values to a column. This default value can be a static value or more commonly generated by a database function like
Constraints on Table
Exclusion constraints, available only in PostgreSQL, ensures that if any two rows are compared on the given columns, at least one of these comparisons will return false or null.
CREATE EXTENSION btree_gist;CREATE TABLE author(ID SERIAL PRIMARY KEY,NAME TEXT,AGE INT,EXCLUDE USING gist(NAME WITH =,AGE WITH <>));
USING gist keyword is used to indicate the type of index to build and use for validation.
The create extension statement needs to be executed once per database.
The unique constraint ensures that no two records in a table have the same value.
CREATE TABLE authors(id SERIAL PRIMARY KEY,name TEXT NOT NULL,email TEXT UNIQUE);
We have marked the
UNIQUE to prevent duplicate entries from being inserted.
In order to drop a constraint, you need to know the constraint name.
ALTER TABLE table_name DROP CONSTRAINT constraint_name;
- Build apps and APIs 10x faster
- Built-in authorization and caching
- 8x more performant than hand-rolled APIs