Constraints are rules you can place on tables to prevent invalid data from being inserted. By using constraints we can prevent columns from being empty, we can ensure values or unique, and much more. In this article, we will learn how to use Constraints in Postgresql.
A list of the most common constraints are as follows:
We will be using docker in this article, but feel free to install your database locally instead. Once you have docker installed, create a new file called
docker-compose.yml and add the following.
version: '3' services: db: image: 'postgres:latest' ports: - 5432:5432 environment: POSTGRES_USER: username POSTGRES_PASSWORD: password POSTGRES_DB: default_database volumes: - psqldata:/var/lib/postgresql phpmyadmin: image: phpmyadmin/phpmyadmin links: - db environment: PMA_HOST: db PMA_PORT: 3306 PMA_ARBITRARY: 1 restart: always ports: - 8081:80 volumes: psqldata:
Now, navigate to
http://localhost:8081/ to access phpMyAdmin. Then log in with the username
root and pass
Click the SQL tab and you are ready to go.
If we want to prevent columns from being empty when inserting rows into the database we can use the NOT NULL constraint. Here is an example of creating a table where we don't allow the last name to be null.
CREATE TABLE employees ( last_name VARCHAR(16) NOT NULL, );
The unique constraint allows us to prevent duplicates for a particular columns. For example, let's update our exmployee table to have unique email addresses.
CREATE TABLE employees ( last_name VARCHAR(16) NOT NULL, email VARCHAR(16) NOT NULL UNIQUE, );
The Primary Key constraint allows us to create a specific column to identify a row. It is common practice to use a column name
id but that is not always the case. There can only be one primary key, unlike unique which we can have many unique columns.
CREATE TABLE employees ( id INT PRIMARY KEY, last_name VARCHAR(16) NOT NULL, email VARCHAR(16) NOT NULL UNIQUE, );
A Frorein Key constraint is placed on a column or multiple columns that ensures they exist in another table. This allows us to keep relationships among tables. For example, we can create a salary table and add a foreign key to ensure an employee exists before we insert a salary.
CREATE TABLE salary ( id INT PRIMARY KEY, amount DECIMAL NOT NULL, emp_id INT REFERENCES employees(id) );
The check constraint allows us to add custom conditions to our columns. For example, we can ensure our salaries don't go below a specific number.
CREATE TABLE salary ( id INT PRIMARY KEY, amount DECIMAL CHECK(SALARY > 10000), emp_id INT REFERENCES employees(id) );
Exclusion constraints are a bit of a special case. They allow you to enforce comparisions and ensure that a comparision will return false or null. We also need the
In this example, we prevent a salary from being inserted with the same amount and emp_id.
CREATE EXTENSION btree_gist; CREATE TABLE salary( id INT PRIMARY KEY, amount DECIMAL CHECK(SALARY > 10000), emp_id INT REFERENCES employees(id) EXCLUDE USING gist (amount WITH=, emp_id WITH=) );