dcsimg
www.webdeveloper.com
Results 1 to 3 of 3

Thread: Foreign Keys with ON DELETE CASCADE

Hybrid View

  1. #1
    Join Date
    Nov 2008
    Posts
    11

    Foreign Keys with ON DELETE CASCADE

    Hi,

    I am writing a MySQL driven web application. I have a master table and some tables referencing the master table. I am using InnoDB as the storage engine and have set up a few foreign keys with 'ON DELETE CASCADE'.

    Having set up the database like this; from PHP, all I need to do is delete the record from the master table and MySQL will delete the related rows automatically. But when I look at the code, it will be very difficult to trace how the data from another table is getting deleted without a query associated to it.

    So coming to my question, is it a good idea to use foreign keys or would it be cleaner to instead write multiple queries to delete the rows? Or should I set up the foreign keys and also run the queries to delete the related rows.

    thanks in advance,
    Arpan D.

  2. #2
    Join Date
    Mar 2005
    Location
    Sydney, Australia
    Posts
    7,974
    The way you are doing it is best since it requires fewer queries and hence allows the database more control to work out the most efficient way of doing the deletes. Anyone who proplery understands how databases work will not have any problem figuring out your code.

  3. #3
    Join Date
    Feb 2003
    Posts
    2,745
    I need to respectfully disagree with Felgall. cascading FKs are one of the worst things you can do to a database.

    It makes for inconsistent code and difficult to trace bugs. No foreign key should ever be able to perform a cascading delete. In fact the very purpose of foreign keys is to prevent this.

    Instead, you should be explicit with ALL of your DML, enlisting transactions if necessary.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles