Understanding PostgreSQL Data Integrity

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:

• Data types limit the type of data that can be stored in a column. The range of values they accept is typically too poor. For example, a column containing the price of a product should probably only accept positive values, but there is no standard data type that accepts only positive numbers in PostgreSQL. Plus, data types can only operate on a single column and cannot implement constraints that involve several columns.
• Triggers are special database functions that are automatically executed by the DBMS when certain events occur, such as INSERT, UPDATE, or DELETE operations on a table. Triggers come in handy in a variety of scenarios, including enforcing complex business rules or maintaining data consistency across multiple tables. At the same time, they are generally too complex for simple data integrity checks. Check out our complete guide to learn more about what SQL triggers are and how to use them.
• Constraints are rules that ensure that data in tables is accurate and consistent. Specifically, constraints limit what can be added or changed in a table, preventing invalid data from being stored. This ensures that all data in the database tables always follow the rules defined by these constraints. PostgreSQL constraints are a powerful tool specifically designed for data integrity.

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!

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.

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

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.

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:

• Physical integrity: Refers to the consistency of data at the physical level. It involves ensuring that the data is stored correctly on disk and that there are no data corruptions from an electronic point of view. Examples of physical integrity checks include checksums and parity checks.
• Logical integrity: Refers to the accuracy of data within a database at the conceptual level. This includes ensuring that the relationships between tables are correctly defined and that the data within each table is consistent. Examples of logical integrity constraints include primary key constraints, foreign key constraints, and check constraints.

What are the five principles of data integrity?

The five principles of data integrity are identified by the acronym ALCOA, which stands for:

• Attributable: Data should be uniquely attributable to the individual or automated system that produced it.
• Legible: Data should be recorded in a form that allows it makes it easy to read and understand.
• Contemporaneous: Data should be stored at the time of its creation.
• Original: Data should be original and not copied from another source unless required.
• Accurate: Data should be accurate, complete, and reliable, without any omissions or errors. This requires some control mechanisms.

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

orders is the table the constraint refers to, and fk_order_user is the name of the constraint that you want to drop. Note that dropping a constraint can lead to data integrity issues.

About the author

Antonello Zanini is a software engineer, and often refers to himself as a technology bishop. His mission is to spread knowledge through writing.