Delete Cascade Not Working with Inherited/Joined Entity

Hi,

I am having issue with Delete. Here is the situation:

  • Company entity extends BaseUuidEntity, Soft delete = false, employees attribute.
  • Employee entity, extends BaseUuidEntity, Soft delete = false, Inheritance strategy = joined, company attribute with On delete inverse = Cascade.
  • FullTimeEmployee entity extends Employee, Soft delete = false.
  • PartTimeEmployee entity extends Employee, Soft delete = false.

When I delete the Company entity, I expect the child employees to be deleted too. But I get the following:

MySQLIntegrityConstraintViolationException: Cannot delete or update a parent row: a foreign key constraint fails (`demo`.`demo_full_time_employee`, CONSTRAINT `FK_DEMO_FULL_TIME_EMPLOYEE_ID` FOREIGN KEY (`ID`) REFERENCES `DEMO_EMPLOYEE` (`ID`))

Is there something I can do to make the system cascade the delete to all employee tables?

Thanks.

Hi Keith,

Since version 6.7, Studio generates foreign key constraints with “on delete cascade” for hard-deleted entities, so your case should work. Could you check how the foreign key is defined in your database?

Here we should consider that sometimes there is a need to delete even soft-deleted entities, as well as linked by JOIN inheritance strategy ones. Let’s say an administrator is received a request to remove records which were created by mistake. Now this is not a piece of cake.

PS If I get it right, Keith wrote about FKs between tables used in JOIN inheritance strategy.

Hi,

The “on delete cascade” exists on the EMPLOYEE table referencing the COMPANY table. But it does not exist on the FULL_TIME_EMPLOYEE table referencing the EMPLOYEE table. It seems that Studio allows setting up the cascade where Employee is an attribute of Company but it does not allow setting up the cascade where FullTimeEmployee extends Employee. So when I delete the Company, I think the framework is following through on the attempt to delete the EMPLOYEE row but breaks down because it is not deleting the FULL_TIME_EMPLOYEE row. To Ilia’s point, I am having trouble with the JOIN inheritance strategy.

From my perspective, the Employee to FullTimeEmployee relationship is one to one and deleting Employee should automatically result in deleting FullTimeEmployee, since they both represent the same employee.

Does this make sense?

Thanks,
Keith

1 Like

Hi Keith,

Now I understand the problem, thank you for the detailed explanation. We’ll fix it, see https://youtrack.cuba-platform.com/issue/STUDIO-4464

Right now you can modify the foreign key generated by Studio in your Init data (30.create-db.sql) script like this:

alter table SAMPLE_FULL_TIME_EMPLOYEE drop constraint FK_SAMPLE_FULL_TIME_EMPLOYEE_ID^

alter table SAMPLE_FULL_TIME_EMPLOYEE add constraint FK_SAMPLE_FULL_TIME_EMPLOYEE_ID 
    foreign key (ID) references SAMPLE_EMPLOYEE(ID) on delete cascade^
1 Like

Thanks for long term and short term fix. You’re the best.

I am currently using platform 7.4 and all my entities are hard delete.
I have set to CASCADE_DELETE option . I am still observing the issue.
I dont see DB scripts getting updated with “ON DELETE CASCADE”.

I am currently using the short term fix. I would like to check on which platform this fix is available

It’s fixed in Studio 6.9. Please do not confuse the platform and Studio versions. The former is a framework and the latter is a tool. This issue concerns only the tool (Studio) that generates DB scripts.

Please provide source code of your entity and the corresponding init scripts from Project > Data Stores > Main Data Store > init > 10.create-db.sql/20.create-db.sql

I am using Studio 9.1 and i am still observing the issue.
Please find the requested files attachedtest.zip (4.7 KB)

Hi,

You have Document that extends StandardEntity. So the Document entity is soft-deleted.
Another point is that you set @OnDeleteInverse(DeletePolicy.CASCADE) to the One-To-Many attributes. Which doesn’t make sense.
For example if you want to delete all Documents when deleting Submission you need to set @OnDeleteInverse(DeletePolicy.CASCADE) on the submissionID attribute of the Document entity.
If both entities are hard-deleted, then foreign key with on delete CASCADE will be generated.

Hi,

I would like to delete all the documents when deleting a submission, so setting a
@OnDeleteInverse(DeletePolicy.CASCADE) on submissionID attribute of Document is sufficient ? I tried this and i dont see any update in the scripts.

As per the document
https://doc.cuba-platform.com/manual-7.0/delete_policy.html
@onDelete is set on onetomany attribute.

Hi,

Yes, setting a @OnDeleteInverse(DeletePolicy.CASCADE) on submissionID attribute of Document is sufficient.
As it was mentioned above you need both entities in relationship to be hard-deleted to set delete policy on database level. So you need to make the Document entity hard-deleted. Now it is not. Change the parent from StandardEntity to BaseUuidEntity and unselect SoftDelete checkbox.