Data Integrity in PostgreSQL: Keeping Your Data Clean and Tidy
When it comes to your database, the integrity of your data is of the enormous importance. After all, the accuracy and reliability of your data is crucial for the smooth operation of your systems. In this article, we will dive into the world of PostgreSQL and explore the various ways in which this powerful database management system ensures the integrity of your data. From primary keys and foreign keys to constraints and transactions, we’ll cover all the tools and techniques you need to keep your data safe and sound. So whether you’re a seasoned database pro or just starting out, this article has something for you. Join me as we explore the ins and outs of data integrity in PostgreSQL.
Real Life Scenario
In this real-life scenario, we have an online marketplace and we keep records of our sales in our PostgreSQL database. In database, there are two tables: “customers” and “orders.” The “customers” table contains information about customers, such as their name, address, and email, while the “orders” table contains information about orders placed by those customers, such as the order date and the product purchased. First of all, we should create our tables. You can create your tables with the code below.
CREATE TABLE customers (
customer_id SERIAL,
name VARCHAR(255),
address VARCHAR(255),
email VARCHAR(255));
CREATE TABLE orders (
order_id SERIAL,
order_date DATE,
customer_id INTEGER,
product_id INTEGER);I need to mention SERIAL data type here. In PostgreSQL, the SERIAL data type is a special type that is used to generate a unique integer value for each record in a table. It is typically used as the primary key for a table. Thanks to the SERIAL data type, you do not need to manually insert the customer_id and order_id values. PostgreSQL does this for you. Isn’t it great?
We have created the simplest table that can be created. Now it’s time to create a database that provides data integrity with step-by-step applications.
Primary Keys
What Is Primary Key?
A primary key is a field or set of fields in a database table that is used to uniquely identify each record in the table such as a social security number . They also help to enforce relationships between tables, as primary keys are often used as foreign keys in other tables to establish connections between different sets of data. Additionally, primary keys can help to improve the performance of queries and indexing in a database. In PostgreSQL, primary keys are typically defined using the “PRIMARY KEY” constraint.
Application in Real Life Scenario
You need to be careful when choosing the column with the PRIMARY KEY in your table. PRIMARY KEY is the token that clearly distinguishes each line from each other. In our example, the columns customer_id in the customers table and orders_id in the orders table will be the PRIMARY KEY. You can use the code below to assign the columns as PRIMARY KEY.
ALTER TABLE orders ADD PRIMARY KEY (order_id);
ALTER TABLE customers ADD PRIMARY KEY (customer_id);Foreign Keys
What Is Foreign Keys?
A foreign key is a field or set of fields in a database table that is used to link data from different tables together and ensure that data is consistent and accurate across different sets of data. Foreign keys are used to In PostgreSQL, foreign keys are typically defined using the “FOREIGN KEY” constraint.
Application in Real Life Scenario
You can add foreing key to your orders table with below code
ALTER TABLE orders ADD FOREIGN KEY (customer_id) REFERENCES customers(customer_id);This foreign key constraint will ensure that every customer_id value in the orders table must match a customer_id value in the customers table.
Constraints
Constraints are rules or conditions that are used to enforce the integrity of data in a database. In PostgreSQL, constraints can be defined at the column level or the table level and are used to enforce rules about the values that can be stored in a database table.
How do constraints work?
Constraints work by specifying rules or conditions that must be followed when inserting, updating, or deleting data in a database table. For example, a constraint might specify that a certain column must contain a unique value for each record, or that a certain column must contain a value within a certain range. If a user attempts to insert, update, or delete data that does not meet the specified constraints, the database will return an error and the operation will fail.
Application in Real Life Scenario
For example, to add a NOT NULL constraint to the name and adress columns of the customers table, This will prevent NULL values from being inserted into the name and address columns.
ALTER TABLE customers ALTER COLUMN name SET NOT NULL;
ALTER TABLE customers ALTER COLUMN address SET NOT NULL;You can also add a unique constraint to the email column of the customers table. This will prevent duplicate values from being inserted into the email column.
ALTER TABLE customers ADD CONSTRAINT email_unique UNIQUE (email);For another constraints, you can use CHECK constraints to set a rule for your data. In this scenario, you can use for product_id should be greater than zero with below code
ALTER TABLE orders ADD CONSTRAINT product_id CHECK (product_id > 0);Also there are lots of other constraints in PostgreSQL like EXCLUDE, COLLATE etc. You can learn more about constraints and how to use them in the PostgreSQL documentation.
Summary
In summary, data integrity is an important aspect of any database. By using these tools and techniques, you can ensure that the data in your PostgreSQL database is consistent, accurate, and reliable, which is essential for the smooth operation of your systems. In PostgreSQL, you can use primary keys, foreign keys, and constraints to enforce the integrity of your data.
