Deleting rows. (Foreign Key association)

[Jayakrishnan_gk]

I have 3 tables. Locations, Units and phases.
Each location can have multiple units and each unit will have multiple phases.
Hence my database structure consists of foreign key associations as follows.
'Units.location_id' references 'Locations.id'
'Phases.unit_id' references 'Units.id'
When I delete a Location, all associated Phases of the Location and all the associated Units of each of these Phase should be deleted.
What would be the best procedure to do this ?
I am using PGSQL and PHP (CodeIgniter )

[pritaeas]

http://www.postgresql.org/docs/8.2/static/ddl-constraints.html

Scroll down to the bottom, see ON DELETE CASCADE

[vee_liang]

practically there are deletion rules as displayed on the links that one of our friend here had attached. in your case if you have already "established" your database and you don't want to re-construct it then you need to create a procedure which works like the "delete cascade" rule. logically, with your limited number of tables that shouldn't be that hard either to reconstruct or to simply just create a procedures.

here is a sample of the trigger procedure in postgre

CREATE OR REPLACE FUNCTION process_del_cscd() RETURNS TRIGGER AS $del_cscd$
    BEGIN

        DELETE FROM target_tbl WHERE SELECT id FROM src_tbl;

        RETURN NULL; -- result is ignored since this is an AFTER trigger
    END;
$del_cscd$ LANGUAGE plpgsql;

CREATE TRIGGER del_cscd
AFTER DELETE ON src_table    FOR EACH ROW EXECUTE PROCEDURE process_del_cscd();