From: Ian Simpson (ianit.myjobgroup.co.uk)
Date: Fri Jan 02 2009 - 03:40:43 CST

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

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

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