Is it possible for a query to delete a record and all of its foreign-key dependents?
I see DROP CASCADE, but not a DELETE CASCADE.
What I'm trying to do: I have a "clients" table. I have many different tables that use the clients.id as a foreign key. When I delete a client, I want it to delete all records in those many different tables that reference this client.
Right now I have my script passing many queries to delete them individually. ("delete from history where client_id=?; delete from payments where client_id=?" -- etc)
You just have to use ON DELETE CASCADE on your foreign key definition in all the table which reference the client.
See http://www.postgresql.org/docs/7.4/interactive/sql-createtable.html and look for FOREIGN KEY and ON DELETE CASCADE. That will have the effect you are looking for. If this is not enough you will have to create a trigger for the scenario.
-- Thomas Braad Toft
---------------------------(end of broadcast)--------------------------- TIP 6: Have you searched our list archives?
http://archives.postgresql.org
