December 3, 2022 By Matthew Rathbone *

A CONSTRAINT is a rule that is used to limit the type of data that can be inserted into a table. In PostgreSQL, constraints are used to specify rules for the data in a table.

A Database Manager That Is Modern, Fast, & Easy To Use

Tried a few tools. Beekeeper was the only one that I found that felt right. Most had a very 1990's feel to them - Allan

I built Beekeeper Studio because, like Allan, I wanted something more intuitive and modern than all the existing clunky apps I could find. My customers agree - they love using Beekeeper and they tell me every day! Give it a try, I bet you'll like it too.

download Download for Desktop
Beekeeper's Linux version is 100% full-featured, no cut corners, no feature compromises.

To add a CONSTRAINT to a table in PostgreSQL, follow these steps:

First, use the ALTER TABLE statement to specify the table that you want to add the constraint to.

ALTER TABLE table_name

Then use the ADD keyword to specify that you want to add a new constraint.

ADD CONSTRAINT constraint_name

Specify the type of constraint that you want to add. There are several types of constraints available in PostgreSQL, including:

  • UNIQUE: This constraint ensures that all values in a column are unique.
  • NOT NULL: This constraint ensures that a column cannot have a NULL value.
  • CHECK: This constraint allows you to specify a condition that must be met by the data in a column.
  • FOREIGN KEY: This constraint is used to enforce a link between the data in two tables.

Finally provide any additional details or options for the constraint, as needed. For example, if you are adding a UNIQUE constraint, you may need to specify the columns that the constraint applies to.

ADD CONSTRAINT constraint_name UNIQUE (column1, column2)

Foreign key constraints

Use the ADD keyword and the FOREIGN KEY constraint to specify the link between the two tables.

ADD CONSTRAINT constraint_name FOREIGN KEY (column1) REFERENCES other_table (column2)

Check constraints

Use the CHECK constraint to specify a condition that the data in a column must meet.

ADD CONSTRAINT constraint_name CHECK (column1 > 0)

Null check constraints

Use the NOT NULL constraint to specify that a column cannot have a NULL value.

ADD CONSTRAINT constraint_name NOT NULL

Unique constraint

Use the ALTER TABLE statement to add the constraint to the table.

ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2);

In this example, we are adding a UNIQUE constraint to the table_name table, on the column1 and column2 columns.

After you have added the constraint, you can use the \d command in the psql terminal to view the details of the table and see the constraints that have been added.

\d table_name

This will display the structure of the table, including the constraints that have been added.

Constraints are Easy in Beekeeper Studio

You can easily view, crate, and modify constraints using Beekeeper Studio, by right clicking a table and selecting View Structure.

Summary

In summary, to add a CONSTRAINT to a table in PostgreSQL, use the ALTER TABLE statement, followed by the ADD keyword and the CONSTRAINT keyword. Then specify the type of constraint that you want to add, and provide any additional details or options.

More Postgresql Articles