Luckily, PostgreSQL does offer us a wide range of constraints such as the primary key, unique constraints, foreign keys, check constraints, exclusion constraints, etc.
This tutorial explores how we can create various constraint types in PostgreSQL. We also cover how to drop an existing constraint from the table using the DROP CONSTRAINT command.
Create Constraints in PostgreSQL
In PostgreSQL, we can add a constraint to an existing table using the ALTER TABLE and the ADD CONSTRAINT clause. Let us explore some common constraints and how to add them to a table.
PostgreSQL Primary Key Constraint
One of the most common and valuable constraints is a primary key. A primary key constraint ensures that a column or a combination of columns can uniquely identify each row in the table.
This ensures that any column that is identified as part of a primary key cannot contain duplicate values.
To add a primary key constraint to an existing table, we can run the query as follows:
ADD CONSTRAINT constraint_name PRIMARY KEY (column_name);
For example, to add a primary key constraint on the id column of the “users” table, we can run the following query:
ADD CONSTRAINT pk_users PRIMARY KEY (id);
This should add a primary constraint called pk_users to the “users” table.
PostgreSQL Unique Constraints
Another type of constraint in PostgreSQL is a unique constraint. This type of constraint ensures that a column or multiple columns only contain unique values.
We can create a unique column as shown in the following syntax:
ADD CONSTRAINT constraint_name UNIQUE (column_name);
For example, we can ensure that only unique emails are added to the “users” table as shown in the following:
ADD CONSTRAINT uc_users_email UNIQUE (email);
PostgreSQL Foreign Key Constraint
One of the standard features of relational databases is the relationship between tables. We use a foreign key constraint to ensure the data integrity across table relationships. The foreign key constraint ensures that the values in a given column match those in the related table’s column.
We can add a foreign key constraint to an existing table using the following query:
ADD CONSTRAINT constraint_name
FOREIGN KEY (column_name)
REFERENCES parent_table_name (column_name);
For example, suppose we want to create a foreign key constraint on the user_id column in the orders table which references the id column in the “users” table.
ADD CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
REFERENCES users (id);
PostgreSQL Check Constraint
A check constraint allows us to define a condition that must be true for each row in a given table.
We can add a check constraint as follows:
ADD CONSTRAINT constraint_name CHECK (condition);
Example:
ADD CONSTRAINT chk_users_age CHECK (age >= 24);
The given constraint ensures that the age of any inserted user is greater than or equal to 24.
Drop Constraints in PostgreSQL
Sometimes, you may need to remove an existing constraint from the table. In that case, we can use the DROP CONSTRAINT command.
The syntax is as follows:
DROP CONSTRAINT constraint_name;
For example, suppose we wish to remove the pk_users constraint that we created earlier. We can run the command as follows:
DROP CONSTRAINT pk_users;
This should drop the constraint and remove the rule enforcement that is defined by the constraint against the data in that table.
Conclusion
Constraints are powerful tools that can help to maintain the data integrity and ensure the correctness of your database. With the help of this tutorial, you can now create various constraint types in your tables.
