Constraints

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

Primary key is used to identify each row of a table uniquely.

Copy
CREATE TABLE author(
  id SERIAL PRIMARY KEY,
  name TEXT
);

The id column has been tagged as the primary key for this table. We will learn more about Primary Keys in the next section.

NOT NULL

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.

Copy
CREATE TABLE author(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL
);

The NOT NULL constraint has been applied to the name column.

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.

CHECK

A CHECK constraint lets you specify a Boolean expression to validate data to be inserted.

Copy
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 CHECK constraint.

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 now() etc.

Constraints on Table

EXCLUSION

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.

Copy
CREATE EXTENSION btree_gist;
CREATE TABLE author(
   ID SERIAL PRIMARY KEY,
   NAME TEXT,
   AGE INT,
   EXCLUDE USING gist
   (NAME WITH =,
   AGE WITH <>)
);

The 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.

UNIQUE

The unique constraint ensures that no two records in a table have the same value.

Copy
CREATE TABLE authors(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT UNIQUE
);

We have marked the email column as UNIQUE to prevent duplicate entries from being inserted.

Dropping Constraints

In order to drop a constraint, you need to know the constraint name.

Copy
ALTER TABLE table_name DROP CONSTRAINT constraint_name;