Postgres Delete Cascade


Click Here And Get This Posted To YOU In PDF Format

Postgres Delete Cascade

Introduction to Postgres Delete Cascade

In Postgres Delete Cascade, we will learn about how we can manage database CRUD operation delete in case of the relational database which contains multiple tables and those tables have foreign key constraints between them. Whenever we perform delete operation in PostgreSQL on a table’s record, it is very necessary to consider whether the table from which record is being deleted has any foreign key relationship with other tables. In this case, mostly the records which are manipulated are from the parent table and the other referenced tables are the child tables. Each record of the parent references one or more records of the child tables.

While performing delete operation it is very important to maintain the consistency of the database tables and their contents. If any child table contains the referenced record to be deleted then it should not happen so. So here is where we can use Delete cascade in PostgreSQL. To learn the delete cascading you need to have PostgreSQL installed on your machine and some basic knowledge of query syntax and query building. You can check whether PostgreSQL is installed or not on your machine by typing this command on command prompt.

psql -V

It is mentioned that the operation performed on the referenced table should behave in a cascading manner for the referencing records while we mention the foreign key constraint in the referencing table using “ON DELETE CASCADE” keywords.

How to use Postgres Delete Cascade

Delete cascade is only used when the tables involve foreign key constraints. Hence, it is first necessary to understand what is the foreign key. Whenever a table contains one or more records with field or group of fields uniquely representing the record of some other table then we say that the first table is said to be referencing table or a child table and the table whose records are being referenced is said to be referenced table or parent table. If we want to avoid manipulation on the referenced records when the referencing records are present in some other tables then we set the foreign keys. When we use the Delete cascade then if the referenced entry is deleted that is parent record is deleted then all its referencing records that are child records are also deleted automatically.

Here, we will learn how we can use delete cascade in PostgreSQL with the help of an example. The example will contain two tables namely teams and developers. Each developer belongs to one or the other team and one team is made of many developers. While maintaining the records of both in the database we will firstly create tables for each one of them.

Step 1: Firstly let us create a table for teams which will be our main referenced parent table.

Code:

CREATE TABLE teams (
id SERIAL PRIMARY KEY,
team_count INTEGER,
department VARCHAR (100)
);

Step 2: Now, we will create a table for developers which will act as the referencing or child table. There will be one too many relationships between teams and developers’ tables. team_id will be our referencing key which will refer to the id of the team table.

Code:

CREATE TABLE developers (
developer_id INTEGER NOT NULL,
team_id INTEGER REFERENCES teams (id),
name VARCHAR (100),
position VARCHAR (100),
technology VARCHAR (100),
PRIMARY KEY (developer_id,team_id)
);

Explanation: The above query will create just a referencing table ‘developers’ for teams. But while deleting the record of the team it will restrict us to delete and not allow if any of the referencing records is present in the developer’s table for the deleting id of the team’s record as the default DELETE value is DELETE RESTRICT.

Step 3: Now, if we want to delete the record from teams with particular id along with all the referencing records of the developer’s table which are referencing the team record which is being deleted then we will use DELETE CASCADE. Here is the syntax using which we can do so while creating the developer’s table.

Code:

CREATE TABLE developers (
developer_id INTEGER NOT NULL,
team_id INTEGER REFERENCES teams (id) ON DELETE CASCADE,
name VARCHAR (100),
position VARCHAR (100),
technology VARCHAR (100),
PRIMARY KEY (developer_id,team_id)
);

Step 4: Now we will insert into the parent table teams some of the records.

Code:

INSERT INTO teams (id, team_count, department) VALUES
(‘1′,’5′,’Accounting’),
(‘2′,’6′,’Inventory’),
(‘3′,’5′,’Human Resource’),
(‘4′,’7′,’CRM’),
(‘5′,’9′,’Bug Solver’),
(‘6′,’4′,’Document’);

Step 5: Let’s insert some referencing records in the child/referencing table of developers.

Code:

INSERT INTO developers (developer_id, team_id, name, position, technology) VALUES
(‘1′,’2′,’Payal’,’senior SD’,’Java’),
(‘2′,’1′,’Heena’,’Developer’,’Angular’),
(‘3′,’2′,’Sayali’,’Developer’,’Hibernate’),
(‘4′,’3′,’Rahul’,’Support’,’Digital Marketing’),
(‘5′,’3′,’Siddhesh’,’Tester’,’Maven’);

Step 6: Let us check whether all the records are inserted correctly in the tables.

SELECT * FROM teams;

Postgres Delete Cascade1

SELECT * FROM developers;

Postgres Delete Cascade2

Step 7: Now we will learn how the delete cascade will be used and perform the delete operation on the above-created tables. In normal case, if we will delete the record from the parent table and child table contains referencing records then it won’t allow deleting but as we are using the DELETE CASCADE, the record from the parent table as well as all the referencing records will be deleted. Now, we will delete all the records with team id as 2 that is Inventory. As DELETE CASCADE is used all the developer records with team id 2 that are Payal and sayali will be deleted automatically.

DELETE FROM teams WHERE id=2;

Let us see the table contents now.

Table teams contain records as follows:

team_count

While developers contain as follows:

devloper_id

Conclusion

In the database structure, it is very important to consider the foreign relationships between the tables while performing any CRUD operations. It is extremely crucial to maintain consistency and cleanliness of the data in the database. If the parent is not there then there should not be any child records that are referencing the deleted records of the parent. As shown above, the DELETE CASCADE can be used in PostgreSQL to delete all the child records whenever the referenced parent record is deleted automatically which helps in maintaining integrity.

Recommended Articles

This is a guide to Postgres Delete Cascade. Here we discuss an introduction to Postgres Delete Cascade, how to use it with programming examples. You can also go through our other related articles to learn more

How to Create a PostgreSQL Schema?
How to Create, Rename, Delete, Copy Table in PostgreSQL
Creating PostgreSQL Views with Examples
Query Examples to Implement TRUNC() in PostgreSQL

The post Postgres Delete Cascade appeared first on EDUCBA.

Read more: educba.com

What's Your Reaction?

Cry Cry
0
Cry
Cute Cute
0
Cute
Damn Damn
0
Damn
Dislike Dislike
0
Dislike
Like Like
0
Like
Lol Lol
0
Lol
Love Love
0
Love
Win Win
0
Win
WTF WTF
0
WTF

Comments 0

Your email address will not be published. Required fields are marked *

Postgres Delete Cascade

log in

Become a part of our community!

Captcha!
Don't have an account?
sign up

reset password

Back to
log in

sign up

Join BoomBox Community

Captcha!
Back to
log in
Choose A Format
Personality quiz
Trivia quiz
Poll
Story
List
Open List
Ranked List
Meme
Video
Audio
Image