'Delete Records from Parent child table

I want to delete records from the database tables and all are in relations.

A -> B -> c-> D

for example : College Table => degrees Table=> Courses Table=> sections table.

Now i want to delete college A from college table and its associated degrees and degrees assoicated with courses and courses associated with sections.

How can i efficiently delete the record.

DB : Postgres Middle tier : SpringBoot with Java1.8

postgresql


Solution 1:[1]

If you define all your foreign key constrains with on delete cascade, Postgres will automatically take care of that.

create table college 
(
  id int primary key, 
  ... other columns ...
);

create table degree 
(
  id int primary key, 
  ... other columns ...
  college_id int not null, 
  constraint fk_degree_college 
     foreign key (college_id) 
     references college (id)
     ON DELETE CASCADE
);

Do the same for the course and section tables. Then if you delete a college, Postgres will automatically delete all referenced rows.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 a_horse_with_no_name

Related Questions