Postgres Constraints
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
Check Constraint
Unique Constraint
Not-null Constraint
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
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 :)