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: jschwa...@the-infoshop.com > To: l...@his.com; mysql@lists.mysql.com > 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:l...@his.com] > >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=jschwa...@the- > >infoshop.com > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > _________________________________________________________________ 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
