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.

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.

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.

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.

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.

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.

ALTER TABLE table_name DROP CONSTRAINT constraint_name;
Did you find this page helpful?
Start with GraphQL on Hasura for Free
  • ArrowBuild apps and APIs 10x faster
  • ArrowBuilt-in authorization and caching
  • Arrow8x more performant than hand-rolled APIs
Promo
footer illustration
Brand logo
© 2024 Hasura Inc. All rights reserved
Github
Titter
Discord
Facebook
Instagram
Youtube
Linkedin