CODEWITHSUNDEEP
javamysqlsqleclipselinkcascade
Rittel
Rittel

Reputation: 589

MySQL cascade on delete changes in wrong direction (eclipselink as JPA)

I ran into some weird issue:

I have a table Constrainable and a table Attribute. Within the Attribute table i specify to which Constrainable the Attribute belongs with a foreign key constraint. I added a CASCADE ON DELETE to that foreign key constraint in the Attribute table.

Now if i want to delete the Attribute the Constrainable is being deleted too.. This should not be happening or am I wrong? I am using this method to do so:

public void remove(IDBObject obj) throws DBException {
    if (manager != null) {
        IDBObject o = null;
        try {
            o = manager.getReference(obj.getClass(), obj.getPrimaryKey());
        } catch (EntityNotFoundException e) {
            throw new DBException("Entity doesnt exist");
        }
        manager.getTransaction().begin();
        manager.remove(o);
        manager.getTransaction().commit();
        return;
    }
    throw new DBException("Manager is closed or null");
}

What could the reasons be for this behaviour?

More detailed outline of the DB:

Constrainable-Table:

| ID |

Attribute-Table:

| ID | Constrainable-ID | Value | <--- Here is the CASCADE ON DELETE defined

Upvotes: 1

Views: 567

Answers (2)

Rittel
Rittel

Reputation: 589

Ok i finally figured out what is going on:

Eclipselink generated some faulty Entity classes in which every Foreignkey Relationship had a (cascade = CascadeType.ALL). After i removed the cascades in the classes where they shouldn't be everything worked fine.

Well that happens if zou are too lazy to write the classes on your own :P I would say one could be expecting that the classes are generated without errors like this. Well I learned my lessons now

Upvotes: 1

Daren
Daren

Reputation: 3417

That is impossible, and as you have described correctly, it should not be happening. Since computers rarely lie there must be a mistake in configuration provoking this behaviour.

  • check the foreign key is correctly defined:

    ALTER TABLE Attribute-Table ADD CONSTRAINT FK_attr_constr FOREIGN KEY (Constrainable-ID) REFERENCES Constrainable-Table (ID) ON DELETE CASCADE ON UPDATE NO ACTION;

  • check Constrainable-Table table does not reference anywhere attribute table, or other tables which might end up referencing attribute. (if you have more than one CASCADE be careful with how those cascades propagate through your schema)

  • lastly remove the ON DELETE CASCADE and see if the same behavior happens, since the cascade should happen when you remove the referenced row from Contrainable-table the deletion of an attribute should have no effect on Constrianable-table.

As a general rule, avoid CASCADE when possible unless you are 100% sure about it because maybe another developer will find this problem when working on another thing if he was not aware of the CASCADE.

Upvotes: 2

Related Questions