ALTER TABLE to add ON DELETE CASCADE statement

Question

I want to do the following in PostgreSQL (using Hibernate):

 ALTER TABLE fruits ADD CONSTRAINTS id ON DELETE CASCADE;

Obviously, my code above is not working, so I am looking for the correct statement.

If I can't do that, then how about the following:

I have a bunch of data in my table fruits. The id field in fruits is used as a foreign key by table grapes. I need to delete a specific row in fruits and I want the deletion to cascade to grapes and delete all entries in grapes that has the specified id. How do I do that?

delete from fruits where id = 1 cascade; 

NOTE: I don't want to do a join and delete the corresponding data in grape. This is just an example. In the real application a large number of tables depend on fruits.

Since I am using Hibernate, for the case when I use a delete statement, can hibernate help do it?
Or can I do this with the information schema or system catalog in PostgreSQL?

Answer 1

What you describe is the textbook foreign key constraint using the ON DELETE CASCADE option.

In SQL you can create it implicitly when creating the table grapes in your scenario:

CREATE TABLE grapes (
  grape_id int PRIMARY KEY
  fruit_id int REFERENCES fruits(fruit_id) ON DELETE CASCADE
  );

Or you can add it later:

ALTER TABLE grapes
ADD CONSTRAINT grapes_fruit_id_fkey FOREIGN KEY (fruit_id)
REFERENCES fruits (fruit_id) ON DELETE CASCADE;

You don't edit the system catalogs directly for that - you hardly ever do! That is what DDL statements like the above are for.

Be aware that a foreign key constraint requires a unique or primary index on the referenced column (fruits.fruit_id in your case) and enforces referential integrity.