CODEWITHSUNDEEP
hibernatepostgresqlcascadecascading-deletespostgresql-9.0
user1467855
user1467855

Reputation: 3993

ALTER TABLE to add ON DELETE CASCADE statement

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?

Upvotes: 2

Views: 12889

Answers (4)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656714

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;

Never edit system catalogs directly for that. Use the dedicated DDL command.

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.

Upvotes: 5

Del Pedro
Del Pedro

Reputation: 1213

Motivation

This did not work for me, I set 

<property name="show_sql">true</property>

and the output did not state anything like ...ON DELETE CASCADE....

I found another solution which worked for me:

Lets assume you have a class for authors and a class for the author's books and you want to auto delete all books whenever you delete the author (via hibernate, sql-query, ...) and there's reasons you cannot (always) delete via session.delete().

Maybe:

session.createSQLQuery("DELETE FROM author").executeUpdate();

Solution

So your author class may look like this:

@Entity
@Table(name = "author")
public class Author {

   @Id
   @GeneratedValue(generator = "increment")
   @GenericGenerator(name = "increment", strategy = "increment")
   @Column(name = "ID")
   private Integer id;

   @Column(name = "NAME")
   private String name;

   @OneToMany(mappedBy = "author")
   private Set<Book> books;
...

and the class book looks like this:

@Entity
@Table(name = "book")
public class Book {

   @Id
   @GeneratedValue(generator = "increment")
   @GenericGenerator(name = "increment", strategy = "increment")
   @Column(name = "ID")
   private Integer id;

   @Column(name = "TITLE")
   private String title;

   @ManyToOne
   @JoinColumn(name = "AUTHOR_ID", foreignKey = @ForeignKey(name = "FK_BOOK_author_AUTHOR_ID"))
   private Author author;
...

The trick is to name the foreign key constraints on your own using 

foreignKey = @ForeignKey(name = "FK_BOOK_author_AUTHOR_ID")

and then add 

<property name="hibernate.hbm2ddl.import_files">update.sql</property>

to your hibernate.cfg.xml (dont forget hibernate.hbm2ddl.auto). The update.sql then contains the update of the table constraints:

ALTER TABLE `book` DROP FOREIGN KEY `FK_BOOK_author_AUTHOR_ID`; 
ALTER TABLE `book` ADD CONSTRAINT `FK_BOOK_author_AUTHOR_ID` FOREIGN KEY (`AUTHOR_ID`) REFERENCES `author`(`ID`) ON DELETE CASCADE;

So hibernate is always able to drop/change the constraints because it knows the name of the constraints - you should also check the @Cascade settings - and you have to implement a strategy on how you handle the deletion of the objects in your session!

Upvotes: 0

user1467855
user1467855

Reputation: 3993

I found the answer:

    //in Fruit object
    @OneToMany(mappedBy = "fruit", orphanRemoval=true)
    private List<Grape> grapes;

   //in Grape object
   @OneToOne
   private Fruit fruit;

Upvotes: 1

atrain
atrain

Reputation: 9255

This is a unidirectional parent-child relationship, where you want a change at the parent to cascade down to the child, but not vice versa. Using annotations, we can accomplish this. In the fruits entity:

@Cascade(value = { org.hibernate.annotations.CascadeType.ALL, 
    org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
@OneToMany(fetch = FetchType.LAZY, mappedBy = "fruit")
public Set<Grape> getGrapes() {
    return grapes;
}

In the 'grape' entity:

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "fruit_id")
public Fruit getFruit() {
    return fruit;
}

When you update or delete the parent fruit, the change will automatically cascade to the grape children.

Upvotes: 1

Related Questions