intro
Let’s explore what tools PostgreSQL offers to enforce data integrity. Follow this tutorial and become an expert on PostgreSQL constraints!
One of the main features offered by relational databases is data integrity, which ensures that the data contained in the database is reliable and consistent. In PostgreSQL, data integrity is enforced through data types, triggers, and constraints. The latter ones are the most powerful tools when it comes to data integrity and includes the popular NOT NULL and FOREIGN KEY constraints.
In this article, you will find out what is data integrity and how to ensure it in Postgres. You will see what the most common PostgreSQL constraints are in detail and how and when to use them.
Let’s dig into PostgreSQL data integrity!
What Is Data Integrity in PostgreSQL?
In PostgreSQL, data integrity refers to maintaining the consistency of the data stored in a Postgres database. Specifically, data integrity ensures that the data contained in database tables is valid, reliable, and accurate. PostgreSQL provides several mechanisms and functions to ensure data integrity. The most popular ones are:
Let’s now look at the different types of constraints in PostgreSQL.
Types of PostgreSQL Constraints
At the time of writing, Postgres supports six types of database constraints to ensure data integrity. Note that you can define constraints through CREATE TABLE or ALTER TABLE queries. If an INSERT or UPDATE query goes against a constraint, PostgreSQL will return a constraint violation error.
Here is the list of all PostgreSQL data integrity constraints.
NOT NULL Constraints
A NOT NULL constraint ensures that a column cannot have NULL values.
For example, the following query specifies that the name and password columns in the users table must always be present.
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
name VARCHAR(50),
4
surname VARCHAR(50),
5
email VARCHAR(250) NOT NULL,
6
password VARCHAR(250) NOT NULL
7
)
If you try to create a user with email or password to NULL, you will get the following error: ERROR: null value in column "name" of relation "users" violates not-null constraint
UNIQUE Constraints
A UNIQUE constraint ensures that all values in a column are different, except for NULL values.
For example, the query below forces all records in the users table to have a different email value.
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
name VARCHAR(50),
4
email TEXT UNIQUE
5
)
PRIMARY KEY Constraints
A PRIMARY KEY constraint ensures that a column or group of columns uniquely identifies each record in a database table. Primary keys do not allow NULL values and must be unique.
For example, the query that follows specifies that the id column is the primary key of the logs table.
1
CREATE TABLE logs (
2
id SERIAL PRIMARY KEY,
3
data JSONB
4
)
FOREIGN KEY Constraints
A FOREIGN KEY constraint ensures that the values in a column or group of columns must match the values appearing in some records of another table. This constraint maintains what is called referential integrity between two tables and implements a relationship between them.
For example, the following query specifies that the customer_id column must reference the id column of the customer table.
1
CREATE TABLE orders (
2
id SERIAL PRIMARY KEY,
3
customer_id INT NOT NULL REFERENCES customers(id),
4
order_date DATE
5
)
CHECK Constraints
A CHECK constraint ensures that all values in a column satisfy some rules.
For example, the query below makes sure that the values in the age column of the users table are always greater than or equal to 18.
1
CREATE TABLE users (
2
id SERIAL PRIMARY KEY,
3
name VARCHAR(50),
4
age INT CHECK (age >= 18)
5
)
EXCLUSION Constraints
An EXCLUSION constraint ensures that if any two rows in a table are compared on the selected column(s) or expression(s) using the specified operator(s), at least one of these operator comparisons will return FALSE or NULL.
For example, the following query ensures that two appointments for the same room cannot overlap.
1
CREATE TABLE appointments (
2
id SERIAL PRIMARY KEY,
3
room_number INT NOT NULL,
4
period TSRANGE NOT NULL,
5
EXCLUDE USING gist (room_number WITH =, period WITH &&)
6
)
How to Check Data Integrity in PostgreSQL
To check data integrity in PostgreSQL, you must verify that the data types, triggers and constraints defined on a table are correct. The best way to do so is to embrace a complete, advanced, fully-featured PostgreSQL client like DbVisualizer!
DbVisualizer allows you to visually explore your PostgreSQL database. Download DbVisualizer, set up a connection to your database, and start to visually inspect your tables!
If you are looking for an easy and powerful SQL client and database manager, then you've got to try DbVisualizer. It connects to nearly any database.
Explore the Column Data Types
Open DbVisualizer, connect to your target database, and in the “Databases” section on the left spot the table you want to check. Right-click on it, choose “Open in a New Tab” and click on the “Columns” tab.
Here, you can visually see and edit all the info associated with each column, including their data types.
This is a great view to verify that the data types and most popular PostgreSQL constraints on a table’s columns are set correctly.
Deal With Triggers
Connect to a database in DbVisualizer, select the target table, and click on the “Triggers” dropdown. This will show you all triggers associated with the selected table. Right-click on the “Triggers” view to access the following view:
Here, you can easily see, edit, add, and delete triggers and make sure that they are accomplishing their data integrity goals.
Defining PostgreSQL Constraints
Start DbVisualizer, reach the table of your interest, right-click on it, and select the “Alter Table…” option.
This will open the following modal:
Here, you can see, add, and drop PostgreSQL constraints on each column of the selected table. Data integrity in PostgreSQL has never been easier!
Conclusion
In this article, you understood what data integrity is and what PostgreSQL offers to enforce it. This includes data types, triggers, and constraints. Specifically, you learned that PostgreSQL constraints are the most complete tool for data integrity and saw how to use the most popular ones. You also saw that checking data integrity is a complex task that requires a complete database client with full PostgreSQL support, such as DbVisualizer. With such an advanced tool, you can visually explore your tables, write optimized queries, and take your database management to the next level. Download DbVisualizer for free!
FAQ About Data Integrity
Can two PostgreSQL constraints have the same name?
No, Postgres constraints must have unique names within the schema they belong to. This means that you cannot have two constraints with the same name in a specific schema. If you attempt to create a constraint with a name that already exists, the DBMS will raise the following error: ERROR: constraint "" for relation "" already exists. To give your constraints meaningful and unique names, it is recommended to follow a naming convention.
What are the two types of data integrity?
There are two types of data integrity:
What are the five principles of data integrity?
The five principles of data integrity are identified by the acronym ALCOA, which stands for:
How to add a foreign key constraint in PostgreSQL?
In PostgreSQL, you can add a foreign key constraint to an existing table through the ALTER TABLE statement as in the example below:
1
ALTER TABLE orders
2
ADD CONSTRAINT order_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id)
The user_id column of the order table will now reference the id primary key column of the user table.
How to drop a constraint in PostgreSQL?
You can drop a constraint in PostgreSQL with an ALTER TABLE query as below:
1
ALTER TABLE orders
2
DROP CONSTRAINT order_user_id_fkey
