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