Constraints in postgres are set of rules which ensures that the stored data is correct. It prevents any invalid data getting introduced in your DB due to application logic or other reasons. They act as a line of defense for the database.

For example - you have an age column in your table. You don’t want age to be negative. So you’ll add a CHECK constraint ensuring it is positive.

Constraints can be applied either for a particular column or for whole table at once. Let’s explore various types of constraints available in Postgres.

---

Types Of Constraints

1. Primary Key Constraint

2. Foreign Key Constraint

3. Check Constraint

4. Unique Constraint

5. Not-null Constraint

6. Exclusion Constraint

---

Primary Key Constraint

PRIMARY KEY identifies each record in a database table uniquely i.e there cannot be two rows with primary column having the same value. There can be only one Primary Key in a table. (Although, there can be multiple UNIQUE constraints). These are generally used as foreign keys in other tables.

CREATE TABLE users (
    id int PRIMARY KEY,
    name text,
    age int,
    wallet_amount float8
);

Here we create the primary key on id. So in users table each entry will have a different ID. Here we use a single column to specify the primary key, we can use multiple columns too for specifying primary key. The id column will automatically become NOT NULL

CREATE TABLE users_multi (
    name text,
    address text,
    age int,
    wallet_amount float8
    PRIMARY KEY (name, address)
);

Here we use name and address and use both these columns in combination as primary key.

In other words, primary key ensures a NOT NULL and UNIQUE constraint.

---

Foreign Key Constraint

Foreign key constraint specifies that the values present in one or more columns must match the values appearing in a row of another table. These are mostly used to create relationships between tables. This is a highly useful feature of relational databases that help in building complex Database systems.

Extending the example above and adding a comment table. Here, each comment must be made by some users. So we add a foreign key for user table pointing to id

CREATE TABLE comment (
    id int PRIMARY KEY,
    user_id int REFERENCES users(id),
    comment_text text,
    upvotes int,
);

---

Check Constraint

In this you basically specify some condition which gives a boolean result. Based on the result -

• true then is inserted in the table.
• false then value is not inserted.

CREATE TABLE users (
    id int PRIMARY KEY,
    name text,
    age int CHECK(age > 0),
    wallet_amount float8
);

In the example query above - we add a check constraint on age where age should be greater than 0 for a value to be inserted in the table.

---

Unique Constraint

As the name suggests, UNIQUE prevents duplicate entries on a particular column(s).

In the example below, we create a user table with a unique constraint on name column. We want to avoid duplicate names in our database.

CREATE TABLE users (
    id int PRIMARY KEY,
    name text UNIQUE,
    age int,
    wallet_amount float8
);

---

Not-null Constraint

A Not-null constraint allows you to specify that a column’s value cannot be null. We need to specify if we don’t want null values since by default columns can have null values.

Continuing the example, let’s add a Not-null constraint on the name column specifying that if name is not provided, then the value will not be inserted in the table.

CREATE TABLE users (
    id int PRIMARY KEY,
    name text NOT NULL,
    age int,
    wallet_amount float8
);

This is one of the most commonly used constraints.

---

Exclusion Constraint

EXCLUDE constraint evaluates a comparison of two rows in the table and ensures that values are not inserted if the condition specified evaluates to false. This constraint is monitored using an index. gist is a common index. You can read more about it here.

Example - We add an exclusion constraint on name such that no two values of name overlap each other. Here && is an operator which must not return true for any pair of rows.

CREATE TABLE users (
    id int PRIMARY KEY,
    name text,
    age int,
    wallet_amount float8,
    EXCLUDE USING GIST (name WITH &&)
);

There is a good explanation regarding exclusion constraint on stackoverflow.

---

Adding constraints

Now that we know about types of constraints, let’s see how to add them. In the previous section, we covered how to add constraints when creating tables. But, what if you need to add constraints later?

For adding constraints later - we can use ALTER TABLE to modify columns and add constraints to them. Suppose we create a users table without any constraints.

CREATE TABLE users (
    id int,
    name text,
    age int,
    wallet_amount float8
);

Now, we will add a NOT NULL constraint on name

ALTER TABLE users ALTER name SET NOT NULL;

Let’s add a UNIQUE constraint to id column

ALTER TABLE users ADD CONSTRAINT unique_user_id UNIQUE (id);

Here, unique_user_id is the name of the constraint. This name can be used to remove constraints directly, let’s see how in the next section.

---

Removing constraints

Although this is rare, in case you want to remove certain constraints due to change in DB architecture and product requirements, you can use ALTER TABLE and simply DROP the constraints. But you need to know the name of the constraint.

ALTER TABLE <your_table_name> DROP CONSTRAINT <constraint_name>;

So, if we want to remove unique_user_id constraint we added in previous section, we will run following query.

ALTER TABLE user DROP CONSTRAINT unique_user_id;

In case you don’t know the name you can remove constraints using ALTER on table and specific columns.

For example - If we want to remove NOT NULL constraint from name then we can run -

ALTER TABLE users ALTER COLUMN name DROP NOT NULL;

---

Resources

• Official Docs
• Postgres constraints - Tutorials Point

I hope you are now ready to use constraints in your databases making them more reliable and secure. Feel free to suggest improvements ✔️

I share regular updates and resources on Twitter. Let’s connect!

Keep exploring 🔎 Keep learning 🚀