0 Replies Latest reply on Apr 14, 2005 12:07 PM by John McLaughlin

    Cascade deletes and self referring entities

    John McLaughlin Newbie


      I have a set of calendar entities in my application, which have relations as follows:

      Calendar-->CalendarMonth 1:n (cascade delete)
      CalendarMonth-->CalendarWeek 1:n (cascade delete)
      CalendarWeek-->CalendarDay 1:n (cascade delete)

      additionally, CalendarDay refers to itself with a 1:1 relation (a reference to another date to compare to). The XDoclet markup for the relation is:

      * @ejb.interface-method view-type="local"
      * @ejb.relation
      * name="calendar_day-cmp20-calendar_day-cmp20"
      * role-name="calendar_day-cmp20-1-has-calendar_day-cmp20-2"
      * target-ejb="CalendarDay"
      * target-role-name="calendar_day-cmp20-2-has-calendar_day-cmp20-1"
      * target-multiple="no"
      * @jboss.relation-mapping style="foreign-key"
      * @jboss.target-relation
      * fk-constraint="true"
      * fk-column="compare_to_id"
      * related-pk-field="nsuk"
      public abstract ie.dcs.roches.interfaces.CalendarDayLocal getCompareTo();

      Other tables, mostly outside the scope of the J2EE app refer to CalendarDay.

      My intention is to use referential integrity to prevent modification of a calendar if any other table in the database has a reference to it. When somebody tries to update a calendar, it is first removed, so if it is being referenced, a RemoveException is thrown, and the transaction aborted.

      This seems to work fine with the exception of the CalendarDay-->CalendarDay relation. For example, if I define a calendar for 2005, then one for 2006, and lets take 2 days 'jan12006', and 'jan12005', where jan12006.compareTo==jan12005. I would expect that any attempt to remove jan12005 would throw a RemoveException (any attempt to remove it with SQL in a DB client does indeed fail). Instead, jan12006.compareToId is set to NULL, and the relation removed (I've set logging to TRACE and can see the FK being nulled).

      Is this expected behaviour? Is there any way of preventing this and forcing referential integrity?

      Any suggestions much appreciated!