ALTER TABLE add 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 doesn't work, so I'm looking for the correct operator.
If I cannot do this, then how about the following:
I have a bunch of data in my table fruits
. Field id
in is fruits
used as a foreign key for the table grapes
. I need to delete a specific line in fruits
, and I want the deletion to cascade to grapes
and delete all records in grapes
with the specified id
. How to do it?
delete from fruits where id = 1 cascade;
NOTE. I don't want to make a connection and delete the corresponding data in the grape
. This is just an example. In a real application, a large number of tables depend on fruits
.
Since I am using Hibernate, for the case when I use the delete operator, can hibernate help me do this?
Or can I do it with information schema or system catalog in PostgreSQL?
source to share
What you are describing is a tutorial on Foreign Key Constraint with Parameter ON DELETE CASCADE
.
In SQL, you can create it implicitly when creating the table grapes
in your script:
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 system directories directly to do this - you almost never do! This is what the DDL says as above.
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 .
source to share
This is a one-way parent-child relationship where you want the changes on the parent to cascade down to the child, but not vice versa. Using annotations, we can do this. In the object fruits
:
@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' object:
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "fruit_id")
public Fruit getFruit() {
return fruit;
}
When you update or remove the parent fruit
, this change is automatically cascaded with the children grape
.
source to share
Motivation
It didn't work for me, I installed
<property name="show_sql">true</property>
and nothing is specified in the output ...ON DELETE CASCADE...
.
I found another solution that worked for me:
Suppose you have a class for authors and a class for books by an author, and you want to automatically delete all books whenever you delete an author (via hibernate, sql-query, ...), and there you can't ( always) delete via session.delete ().
May be:
session.createSQLQuery("DELETE FROM author").executeUpdate();
Decision
So your author class might 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 cool 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 specify foreign key constraints yourself 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 (don't forget hibernate.hbm2ddl.auto). Then update.sql 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;
This way hibernate can always drop / modify constraints as it knows the name of the constraints - you should also check the @Cascade settings - and you need to implement a strategy for how you handle deleting objects in your session!
source to share