List:General Discussion« Previous MessageNext Message »
From:Martin Gainty Date:January 3 2009 2:41am
Subject:RE: How to Use Cascade Delete Properly
View as plain text  
If you're not going to Cascade Delete you'll need to change ON DELETE CASCADE clause to ON
DELETE SET NULL e.g.
change
FOREIGN KEY(id_b) REFERENCES b(id) ON DELETE CASCADE
TO
FOREIGN KEY(id_b) REFERENCES b(id) ON DELETE SET
NULLhttp://dev.mysql.com/doc/refman/5.0/en/delete.html

Martin 
______________________________________________ 
Disclaimer and confidentiality note 
Everything in this e-mail and any attachments relates to the official business of Sender.
This transmission is of a confidential nature and Sender does not endorse distribution to
any party other than intended recipient. Sender does not necessarily endorse content
contained within this transmission. 




> From: jschwartz@stripped
> To: lola@stripped; mysql@stripped
> Subject: RE: How to Use Cascade Delete Properly
> Date: Fri, 2 Jan 2009 10:24:14 -0500
> 
> 
> 
> >-----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
> 
> 
> 
> 
> 
> -- 
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
> 

_________________________________________________________________
It’s the same Hotmail®. If by “same” you mean up to 70% faster.
http://windowslive.com/online/hotmail?ocid=TXT_TAGLM_WL_hotmail_acq_broad1_122008
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