BlogNewsletterTwitter

CHECK constraint v/s EXCLUSION constraint

Posted on 22 Jul, 2022

CHECK constraint

CHECK checks whether a record being entered matches the boolean criteria or not. If the record violates that condition, it is not entered into the table.

CREATE TABLE PEOPLE(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     CHECK(AGE > 16),
   ADDRESS        CHAR(150),
);

EXCLUSION constraint

EXCLUSION lets you enforce a constraint on multiple rows (for a given column). It let's you validate what data is valid for a row based on other rows that exist within the table.

Exclusion constraints tell the database (conceptually) "When you're given a new or updated row, compare it against each existing row. Here's a list of comparisons to use. If all the comparisons between the new data and an existing row return true, reject the new data.

This constraint use btree indexes to do comparisons at a large scale. In PostgreSQL you have to create the btree_gist extension to use the EXCLUSION constraint.

CREATE EXTENSION btree_gist

Let's see how to use this constraint

CREATE TABLE PEOPLE(
   ID INT PRIMARY KEY     NOT NULL,
   NAME           TEXT    NOT NULL,
   AGE            INT     NOT NULL,
   ADDRESS        CHAR(150),
   EXCLUDE USING gist (ADDRESS with =)
);

Note that if you specify "=", the semantics are equal to UNIQUE constraint. The magic lies in using multi-column constraints. For e.g.

EXCLUDE USING gist (a with =, b with &&)

Here Row1 will conflict with Row2, IFF:

  1. Row1.a == Row2_.a, and

  2. Row1.b && Row2.b

You can utilize other PostgreSQL geometric operators here to build more complex constraints.

Resources

PreviousWhy Reverse ETLNextClustered & Non-clustered Indexes

Last updated