CODEWITHSUNDEEP
sqlnode.jspostgresqlknex.js
Shaheryar ahmed
Shaheryar ahmed

Reputation: 111

How to disable soft delete in parent table when it has associated data in child table

I am soft-deleting data using a binary check is_deleted field in db tables.I have country(parent) and city(child) tables in db.The problem is if user try to soft-delete a country which has associated data in city table,it should not allow user to delete it

fields in country table: id,name,is_deleted

fields in city table: id,name,is,is_deleted,country_id(foreign key)

Creating sample data:

insert into country(id,name,is_deleted) values(1,'United Stated', false)

insert into city(id,name,is_deleted,country_id) values(1,'Washington',false,1)

trying to soft-delete from country which has associated data in child

update country set is_deleted = true where id = 1

On soft-deletion it should give me error or some message

Upvotes: 1

Views: 248

Answers (2)

Belayer
Belayer

Reputation: 14861

@LaurenzAlbe provides a pretty cool solution, but maybe a little too awkward. Perhaps a trigger would be a little less so (though I generally dislike triggers). Either way a point you need to consider: What happens when I insert a city for a country that has been flagged as deleted, and the new city is not flagged as deleted? See fiddle.

create or replace
function validate_all_cities_deleted()
  returns trigger 
  language plpgsql
as $$ 
declare nl constant varchar(1) := chr(10);
begin 
    if exists  
       ( select null  
           from city 
          where country_id = new.id
            and not is_deleted
       )
    then 
       raise exception 'Business Rule Violation:% Cannot "delete" % while it has at least 1 non-deleted city.',nl,new.name   
       using 
          hint = format ('Check all cities with with country_id = %s (%s), set their is_deleted = True, then retry',new.id,new.name)
       ;
end if; 

    return new; 
end; 
$$; 

create trigger remove_country_city_bur
  before update of is_deleted 
  on country 
  for each row 
  execute function validate_all_cities_deleted();

Upvotes: 0

Laurenz Albe
Laurenz Albe

Reputation: 246268

The only way I can think of is slightly awkward:

/* add a column that mirrors "is_deleted" from "cuntry" */
ALTER TABLE city ADD country_is_deleted boolean DEFAULT FALSE NOT NULL;

/* this will be the target of the foreign key, no other purpose */
ALTER TABLE country ADD UNIQUE (id, is_deleted);

/* make sure "country_id" and "country_is_deleted" match the values
   in "country", and that every soft delete in "country" is replicated */
ALTER TABLE city ADD FOREIGN KEY (country_id, country_is_deleted)
                     REFERENCES country (id, is_deleted)
                     ON UPDATE CASCADE;

/* make sure no country can be deleted when the city is not deleted */
ALTER TABLE city ADD CHECK (country_is_deleted IS FALSE OR is_deleted IS TRUE);

Upvotes: 1

Related Questions