Updated May 18, 2023
Introduction to Postgres Delete Cascade
In Postgres Delete Cascade, we will learn how to manage database CRUD operation delete in case of a relational database that contains multiple tables with foreign key constraints between them. When deleting records from a table in PostgreSQL, it is crucial to consider whether it has any foreign key relationships with other tables. In this scenario, users typically prioritize manipulating records in the parent table, regarding the referenced tables as child tables. Each record of the parent references one or more records of the child tables.
While performing a delete operation, it is essential to maintain the consistency of the database tables and their contents. If any child table contains the referenced record to be deleted, it should not happen. So here is where we can use the 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. To verify the installation of PostgreSQL on your machine, you can execute the following command in the command prompt.
psql -VWhen we specify the foreign key constraint in the referencing table using the “ON DELETE CASCADE” keywords, it indicates that the operation performed on the referenced table should cascade to the referencing records.
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. When we come across a table containing one or more records, where a field or group of fields uniquely represents records from another table, we assign the label of “referencing table” or “child table” to the first table and the label of “referenced table” or “parent table” to the table whose records are being referenced. We can set up foreign keys to prevent manipulation of referenced records when there are corresponding referencing records in other tables. When we use the DELETE CASCADE feature, deleting the referenced entry (parent record) results in the automatic deletion of all its referencing records (child records).
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 both records in the database, we will first 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. When deleting a team record, the presence of referencing records in the developer’s table will prevent deletion due to the default DELETE value being DELETE RESTRICT.
Step 3: Now, if we want to delete the record from teams with a 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;
SELECT * FROM developers;
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 delete the record from the parent table and child table containing referencing records, then it won’t allow deleting. However, as we are using the DELETE CASCADE, the record from the parent table and all the referencing records will be deleted. Now, we will delete all the records with team id as 2, that is, Inventory. Using DELETE CASCADE, developer records with team ID 2, like 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:
While developers contain as follows:
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
We hope that this EDUCBA information on “Postgres Delete Cascade” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
