JPA not generating "on delete set null" FK restrictions.

Question

I have two related clases JPA-annotated. Alarm and Status. One Alarm can have one Status.

What I need is to be able to delete one Status and "propagate" a null value to the Alarms that are in that Status that has been deleted.

That is, I need the foreign key to be defined as "on delete set null".

@Entity
public class Alarm {
    @Id
    @GeneratedValue(strategy=GenerationType.SEQUENCE, generator="sequence")
    @SequenceGenerator(name="sequence", sequenceName="alarm_pk_seq")
    private Integer id;

    @OneToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="idStatus")
    private Status status;

    // get/set
}

@Entity
public class Status {
    @Id
    @Column(name="idStatus")
    private Integer id;

    private String description;

    // get/set
}

Example:

Before:

STATUS
id  description
1   new
2   assigned
3   closed

ALARMS
id  status
1   1
2   2
3   2

After (deleting the status with id=2)

STATUS
id  description
1   new
3   closed

ALARMS
id  status
1   1
2   NULL
3   NULL

I am using Hibernate and PostgreSQL, automatically generating the database from source code. I have tried with every possible CascadeType with no success.

Is there anything wrong in the code ? Is it possible to do it with JPA ?

Answer 1

Hello,

Are you sure with your @OneToOne? It seems to me that you'd rather use @ManyToOne (as a status can be affected to several alarms):

@Entity
public class Alarm {
    ...

    @ManyToOne(cascade=CascadeType.ALL)
    @JoinColumn(name="idStatus", nullable=true)
    private Status status;

    ...
}

Answer 2

Just add that using the Hibernate annotation:

@OnDelete(action=OnDeleteAction.CASCADE)

generates the foreign key as : "ON UPDATE NO ACTION ON DELETE CASCADE;"

But there is no action=OnDeleteAction.SET_NULL

Moreover, I don't like to tie my code to Hibernate if possible (but I can live with it if it works).

This thread discusses it. I can't believe there is not an easy method in JPA (or Hibernate extensions) to generate the foreign key.

Answer 3

In your case, you are generating the database from the classes. This would imply that you wont use the database for other purposes (as doing so would force you to have DDL scripts). That means that have this rule implemented in the database or in the java code is unimportant.

We also know that Hibernate would raise a transient exception in the case where one would delete one or more statuses and try to reference it when committing without a cascade.

Also, the database will be generated using a foreign key constraint.

All that means that the constraint MUST be respected for the application to work.

If your entities are in a jar by themselves, you could add a transient method to the alarm or the status interface to remove a status while respecting the rule.

Also, the programmers, when using the entities will be forced to respect the rule or else the code wont work. But to make the task easier, you could make the relation bidirectional so that the task of tracking down the alarms from the statuses is made easier.

If you can, use an ondelete interceptor/listener to set the alarm.status property to null.

Answer 4

Don't know about other non-hibernate implementations, but here is a JIRA issue I've been following about this in Hibernate...

http://opensource.atlassian.com/projects/hibernate/browse/HHH-2707