Get latest articles directly in your inbox
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
Primary Key Constraint
Foreign Key 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
CREATE TABLE users_multi ( name text, address text, age int, wallet_amount float8 PRIMARY KEY (name, address) );
Here we use
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
CREATE TABLE comment ( id int PRIMARY KEY, user_id int REFERENCES users(id), comment_text text, upvotes int, );
In this you basically specify some condition which gives a boolean result. Based on the result -
truethen is inserted in the table.
falsethen 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.
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 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.
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.
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
ALTER TABLE users ALTER name SET NOT NULL;
Let’s add a
UNIQUE constraint to
ALTER TABLE users ADD CONSTRAINT unique_user_id UNIQUE (id);
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.
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;
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 🚀
Liked the content? Do support :)