From: Jerry Schwartz (jschwartzthe-infoshop.com)
Date: Fri Jan 02 2009 - 09:24:14 CST

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

>-----Original Message-----
>From: Lola J. Lee Beno [mailto:lolahis.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=jschwartzthe-
>infoshop.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]