I have two tables A & B. A has a one-to-many relationship with B, and also keeps a many-to-one relationship to record the last B element. B keeps a many-to-one reference back to A as the parent entity.

A
id = 1
lastB = 2

B
id = 2
A = 1

As part of my test environment, I have an InitDB.java script, that calls session.delete() and then initializes the database.

When I use session.delete("from B in " + B.class); I get the flush/cascade error.

Two things I have questions on:

1) I'd like to know how to get the constraint set up so that when the row in B is deleted, it automatically sets A.lastB to null

2) Is using session.delete("...") the best way to delete an entire database? Or should I just keep dropping/recreating tables from a .sql file like I'm doing today?

Don't use Hibernate (or any other ORM solution, probably) for mass updates/deletes. Thats not what ORM is really _for_.

Refer to your docs, but this should work with PostgreSQL
ALTER TABLE a
ADD ( FOREIGN KEY (lastB)
REFERENCES b (id) ON DELETE SET NULL) ;

Thanks Gavin - I'll move to plain JDBC calls in the initialize script.

Thanks Russell - this is what I was looking for. Now, I use xdoclet tags to generate my mapping files (.hbm) and the SQL schema (SchemaExport). Is there any way to specify using hibernate and/or xdoclet that the 'on delete set null' be added to the generated alter table ... references statement?

Or would I need to make changes to the PostgreSqlDialect?

Hi,

although this thread died quite a long time ago I'll ask again:
is there a way to specify ON DELETE SET NULL constraint for many-to-one relationship in the mapping file?

Thanks for any reply,
julius