From: Martin Gainty (mgaintyhotmail.com)
Date: Fri Jan 02 2009 - 20:41:33 CST

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

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: jschwartzthe-infoshop.com
> To: lolahis.com; mysqllists.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: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
> To unsubscribe: http://lists.mysql.com/mysql?unsub=mgaintyhotmail.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

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