Posted on 2006-07-10
OK - small background is - I am working in phpMyAdmin with tables. I have set up the foreign key constraints but have thus far avoided messing with any of the cascade delete stuff because I didn't really understand it thoroughly. I understand the premise, but wasn't comfortable with messing with things.
Just tried to reload the database though from a mysqldump by dropping the tables if they exist and inserting the new rows, and it won't let me do that because it is saying "Cannot delete or update a parent row: a foreign key constraint fails" - so I know it's that the cascades aren't set.
SO - I am hoping you can give me a 101 on how to accomplish this based on a couple tables. If I understand a couple, I think I can get the rest.
The tables are the following: basically an address table that references FK's to a state table and a country table - I guess my question is, is the cascade from the parent table down? So if I deleted a state in my state table like 'CO' and addresses has some rows in it that reference 'CO', it is going to cascade down to those addresses? So in my address table I would set ON DELETE CASCADE and then it would delete the entire row in address when I delete a state? Am I understanding that correctly?
DROP TABLE IF EXISTS `address`;
CREATE TABLE `address` (
`address_id` smallint(6) NOT NULL auto_increment,
`street` varchar(60) NOT NULL default '',
`city` varchar(30) NOT NULL default '',
`state_id` smallint(6) default NULL,
`zip` varchar(20) default NULL,
`iso` char(2) default NULL,
PRIMARY KEY (`address_id`),
KEY `state_id` (`state_id`),
KEY `iso` (`iso`)
) TYPE=InnoDB AUTO_INCREMENT=1 ;
DROP TABLE IF EXISTS `country`;
CREATE TABLE `country` (
`iso` char(2) NOT NULL default '',
`name` varchar(80) NOT NULL default '',
`printable_name` varchar(80) NOT NULL default '',
`iso3` char(3) default NULL,
`numcode` smallint(6) default NULL,
PRIMARY KEY (`iso`)
DROP TABLE IF EXISTS `states`;
CREATE TABLE `states` (
`state_id` smallint(6) NOT NULL auto_increment,
`name` varchar(40) NOT NULL default '',
`abbrev` char(2) NOT NULL default '',
PRIMARY KEY (`state_id`)
) TYPE=InnoDB AUTO_INCREMENT=66 ;