>-----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=arch...@jab.org
