List:General Discussion« Previous MessageNext Message »
From:Ian Simpson Date:January 2 2009 9:40am
Subject:Re: How to Use Cascade Delete Properly
View as plain text  
If you want deletes to be blocked, then you shouldn't be using ON DELETE
CASCADE; the default behaviour is ON DELETE CONSTRAIN, which seems to be
what you want; it will refuse to delete any rows that are depended on by
rows in other tables.

On Thu, 2009-01-01 at 17:07 -0500, Lola J. Lee Beno wrote:
> 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: 
> 
> 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
> 
> 
-- 
Ian Simpson
Award Winning System Administrator
MyJobGroup

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