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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
