• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Cascade delete references from multiple related tables.

 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a log table that is relating to the complaint, maintainence and other tables that must maintain a log.
This log table has nothing to do with the system tracking stuff. It is just used for reminder purposes.
Like when a complaint or maintaince follow up is due, they are displayed on the front page of the application.
So whenever those complaints and maintainces records are to be deleted, these logs should be wiped out too.
So my question how do I define a JavaDB declaration on the .sql initialization file, so that it can be "cascadely" deleted when
complaint and maintainence records are deleted?



Thanks
Jack

 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You can have several columns referring to several different foreign (parent) tables, all (or just some) of them declared with CASCADE DELETE. If any of the parent records of the CASCADE DELETE relationship gets deleted, the child records get deleted too.

If you want to refer two tables (say, complaints and maintenance) and only delete the child record when both parent records are deleted (so, not just one of them), that would be more difficult and cannot be expressed using foreign key constraints. The two parent tables (complaints and maintenance) would be related by sharing a common child. Perhaps some other way could be used to design your model, but without better knowledge of your requirements we can't help much. Is this what you need?
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Martin,
Basically, my requirement is simpler than that,
Only one child record exists for either maintainence or complaint, not both at the same time.
But when a maintainence record is deleted, the corresponding log record should be deleted.
And so forth for the complaint record, they don't really share the common child record.
when you say to have several columns, you mean to have two ids, one for maintainence, one for complaints?
Thanks
Jack
 
Martin Vajsar
Sheriff
Posts: 3752
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Jacky Luk wrote:when you say to have several columns, you mean to have two ids, one for maintainence, one for complaints?

Yes. The foreign key constrain doesn't allow to declare a column that would refer to the union of two tables (this is what you would need with just a single column).

The log table should have two columns, say, maintenance_id and complaint_id, that will be declared with the foreign key constraint referring to the appropriate table, with the CASCADE DELETE option. However, these columns must not be declared NOT NULL, since only one of these columns will contain a non-null value, depending on the type of the parent record. You shouldn't need more than this.

(Note: please don't quote entire posts. Quote is useful if you want to highlight just some part of the post, or when referring to an older post, which wouldn't otherwise be apparent. Quoting unnecessarily increases clutter and makes the threads less comprehensible. Thanks.)
 
Jacky Luk
Ranch Hand
Posts: 634
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, many thanks, Martin, I'll go with what you said
Jack
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic