List:General Discussion« Previous MessageNext Message »
From:Lola J. Lee Beno Date:January 1 2009 10:07pm
Subject:How to Use Cascade Delete Properly
View as plain text  
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

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