-1

So I have five tables: job , category, job_category, client and job_client. job_client and job_category have an FK of job_id which references job table. Those FK's are set to ON DELETE NO ACTION. What I'm trying to figure out is whats a good way to program this so that I can click a button to delete a job from the job table, but it automatically goes and delete its references in the job_category and job_client first, then delete the job. I'm using MySQL and PHP. Thanks.

Patrick Traile
  • 83
  • 6
  • so you want child cascading deletes? – Drew Sep 20 '16 at 00:53
  • So I must use cascading deletes for this, there isn't a way to it that effect with DELETE NO ACTION? – Patrick Traile Sep 20 '16 at 01:03
  • You can delete manually – Drew Sep 20 '16 at 01:05
  • I was trying to come up with some sort of solution where when I say delete a job the logic knows to go and delete references in other tables first then come back and delete the job. – Patrick Traile Sep 20 '16 at 01:08
  • Well as it is such a common use case, they baked it into InnoDB with FK's and cascading deletes to spare the dev of doing such a thing. Which also encourages having FK's in the first place and Referential Integrity – Drew Sep 20 '16 at 01:09
  • 1
    so you're saying cascading deletes is the way to go for what i'm trying to do, I can change the delete type after the table is created right. Will check out using cascade deletes – Patrick Traile Sep 20 '16 at 01:17

1 Answers1

1

Those FK's are set to ON DELETE NO ACTION. 

Set all to ON DELETE  CASCADE.

IT will delete all foreign keys from any tables.

More info:

In outside world, deletion of record is not advisable, use tagged deleted 1 or 0 instead.

Vijunav Vastivch
  • 4,153
  • 1
  • 16
  • 30
  • tagged deleted? how does that work? and how would I apply that to my situation? I have some stuff on the front end that show be removed when I remove a job from job table. – Patrick Traile Sep 20 '16 at 01:02
  • I think he means do a soft delete. Mark it, filter. – Drew Sep 20 '16 at 01:05
  • @Drew soft delete, hhmmm, never encountered this before will do some research. – Patrick Traile Sep 20 '16 at 01:09
  • Well it is like when you delete your question or answer with the delete button thingie. That is a soft delete. `isActive int not null` ... 1 show it. 0 don't – Drew Sep 20 '16 at 01:10
  • oh jus some boolean swith thing, cool so its still in the db, ok got it – Patrick Traile Sep 20 '16 at 01:16
  • @Patrick is problem solved? – Vijunav Vastivch Sep 20 '16 at 02:02
  • @reds nope not yet reading up on cascading deletes and best practices for deleting from mysql – Patrick Traile Sep 20 '16 at 03:16
  • best practice do not delete a record from db, use the above field as tagging 0 or 1. 1 means deleted 0 means not deleted. by default use this field value to 0 – Vijunav Vastivch Sep 20 '16 at 03:45
  • @reds would you recommend creating a separate table for deleted records (soft delete) and if so, could you give tips on implementing this also, if I use soft delete how do I manage those foreign key relationships to tag them as deleted also – Patrick Traile Sep 20 '16 at 16:06
  • All foreign keys will stay and depends on deteled tag if 0 or 1. you can create a separate table on it its up to you, like primarykeyid, deleted for a separate table or add another column into primary table called deleted int(1) default 0. This all record depends on deleted field if 0 or 1. – Vijunav Vastivch Sep 21 '16 at 00:30
  • As a result all records will stay and it all depends on your query in condition where deleted = 0 – Vijunav Vastivch Sep 21 '16 at 03:19