List:General Discussion« Previous MessageNext Message »
From:Jerry Schwartz Date:January 2 2009 3:24pm
Subject:RE: How to Use Cascade Delete Properly
View as plain text  

>-----Original Message-----
>From: Lola J. Lee Beno [mailto:lola@stripped]
>Sent: Thursday, January 01, 2009 5:08 PM
>To: 'MySQL'
>Subject: How to Use Cascade Delete Properly
>
>I'm trying to understand how to use cascade delete properly but not sure
>if I have this backwards or not.  Here's an example:
>
[JS] Unless I'm misunderstanding, your problem is that you are thinking about 
foreign keys when it isn't necessary. To delete a record only if no dependent 
records exist, just

DELETE master FROM master LEFT JOIN dependent ON master.master_key = 
dependent.master_key WHERE master.master_key = some_value AND 
dependent.master_key IS NULL;

>I have two tables:
>
>mysql> describe adsource;
>+-------------+--------------+------+-----+---------+-------+
>| Field       | Type         | Null | Key | Default | Extra |
>+-------------+--------------+------+-----+---------+-------+
>| adsource_id | varchar(35)  | NO   | PRI | NULL    |       |
>| company_id  | varchar(35)  | YES  | MUL | NULL    |       |
>| location    | varchar(50)  | YES  |     | NULL    |       |
>| url         | varchar(200) | YES  |     | NULL    |       |
>+-------------+--------------+------+-----+---------+-------+
>
>
>mysql> describe jobposts;
>+--------------+--------------+------+-----+---------+-------+
>| Field        | Type         | Null | Key | Default | Extra |
>+--------------+--------------+------+-----+---------+-------+
>| jobpost_id   | varchar(35)  | NO   | PRI | NULL    |       |
>| company_id   | varchar(35)  | NO   | MUL | NULL    |       |
>| details      | text         | YES  |     | NULL    |       |
>| job_title    | varchar(50)  | YES  |     | NULL    |       |
>| postdate     | date         | YES  |     | NULL    |       |
>| salary       | decimal(5,2) | YES  |     | NULL    |       |
>| deadlinedate | date         | YES  |     | NULL    |       |
>| adsource_id  | varchar(35)  | YES  | MUL | NULL    |       |
>+--------------+--------------+------+-----+---------+-------+
>
>
>
>For jobposts; I have adsourcefk referencing adsource.adsource_id with
>cascade delete set. For adsource, I have companyfk referencing
>company.company_id with cascade delete set.
>
>Now, say I have three jobposts records that have one referenced adsource
>record.  If I delete one jobposts record, there now remains two jobposts
>records.  If I delete the adsource record, the two jobposts records get
>deleted. I don't want this happening.
>
>What I want to have happen is: if I try to delete an adsource record and
>there are jobposts records containing that id as foreign key, I want the
>delete to NOT happen.  I can't use triggers because for some reason I
>can't get the triggers working properly.  Is there any way I can do this
>on the database side without having to write code in the application
>code I'm working on?
>
>--
>Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire
>http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/
>"No greater injury can be done to any youth than to let him feel that
>because he belongs to this or that race he will be advanced in life
>regardless of his own merits or efforts." - Booker T. Washington
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>infoshop.com




Thread
How to Use Cascade Delete ProperlyLola J. Lee Beno1 Jan
  • Re: How to Use Cascade Delete ProperlyIan Simpson2 Jan
    • Re: How to Use Cascade Delete ProperlyLola J. Lee Beno2 Jan
      • Re: How to Use Cascade Delete ProperlyIan Simpson2 Jan
  • RE: How to Use Cascade Delete ProperlyJerry Schwartz2 Jan
    • RE: How to Use Cascade Delete ProperlyMartin Gainty3 Jan