PurpleSlade
asked on
CASCADE confusion
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`)
) TYPE=InnoDB;
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 ;
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`)
) TYPE=InnoDB;
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 ;
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>So is it generally a good idea to set everything to cascade in the child tables?
this is a choice to be made. with important data I would NOT allow automatic deletion of the child records, but prevent deletion with the foreign key constraints (that's what they are for).
this is a choice to be made. with important data I would NOT allow automatic deletion of the child records, but prevent deletion with the foreign key constraints (that's what they are for).
ASKER
Snoyes, that worked with the first table from the dump, but it looks like it didn't work for the next one -- it created address, but for address_type (the next in line in the dump file) I get the following error:
C:\>mysql -u root -p mydb < mydb.sql
Enter password: ******
ERROR 1005 (HY000) at line 46: Can't create table '.\mydb\address_type
.frm' (errno: 150):
-- phpMyAdmin SQL Dump
-- version 2.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 10, 2006 at 05:17 PM
-- Server version: 4.0.27
-- PHP Version: 4.3.11
--
-- Database: `mydb`
--
SET FOREIGN_KEY_CHECKS=0;
-- --------------------------
--
-- Table structure for table `address`
--
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 ;
--
-- Dumping data for table `address`
--
-- --------------------------
--
-- Table structure for table `address_type`
--
DROP TABLE IF EXISTS `address_type`;
CREATE TABLE `address_type` (
`address_type_id` char(3) NOT NULL default '',
`description` varchar(20) default NULL,
PRIMARY KEY (`address_type_id`)
) TYPE=InnoDB;
--
-- Dumping data for table `address_type`
--
INSERT INTO `address_type` (`address_type_id`, `description`) VALUES ('HMP', 'Primary Home Address');
-- --------------------------
C:\>mysql -u root -p mydb < mydb.sql
Enter password: ******
ERROR 1005 (HY000) at line 46: Can't create table '.\mydb\address_type
.frm' (errno: 150):
-- phpMyAdmin SQL Dump
-- version 2.8.1
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jul 10, 2006 at 05:17 PM
-- Server version: 4.0.27
-- PHP Version: 4.3.11
--
-- Database: `mydb`
--
SET FOREIGN_KEY_CHECKS=0;
-- --------------------------
--
-- Table structure for table `address`
--
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 ;
--
-- Dumping data for table `address`
--
-- --------------------------
--
-- Table structure for table `address_type`
--
DROP TABLE IF EXISTS `address_type`;
CREATE TABLE `address_type` (
`address_type_id` char(3) NOT NULL default '',
`description` varchar(20) default NULL,
PRIMARY KEY (`address_type_id`)
) TYPE=InnoDB;
--
-- Dumping data for table `address_type`
--
INSERT INTO `address_type` (`address_type_id`, `description`) VALUES ('HMP', 'Primary Home Address');
-- --------------------------
>this is a choice to be made.
to give you an example of where I would use it:
ORDERS <- ORDER_DETAILS (CASDADE DELETE)
simply deleting the order will delete it's child items along.
but NOT on this:
PRODUCTS <- ORDER DETAILS
you should not be able to delete a product if you have order details on it.
you might "close" the product (make it unavailable), but keep it for reference.
to give you an example of where I would use it:
ORDERS <- ORDER_DETAILS (CASDADE DELETE)
simply deleting the order will delete it's child items along.
but NOT on this:
PRODUCTS <- ORDER DETAILS
you should not be able to delete a product if you have order details on it.
you might "close" the product (make it unavailable), but keep it for reference.
ASKER
>So is it generally a good idea to set everything to cascade in the child tables?
this is a choice to be made. with important data I would NOT allow automatic deletion of the child records, but prevent deletion with the foreign key constraints (that's what they are for).
So then really, I should probably not be changing everything to cascade delete just because it is not dropping the table in a reload. Ok - I think I understand now.
this is a choice to be made. with important data I would NOT allow automatic deletion of the child records, but prevent deletion with the foreign key constraints (that's what they are for).
So then really, I should probably not be changing everything to cascade delete just because it is not dropping the table in a reload. Ok - I think I understand now.
>I should probably not be changing everything to cascade delete just because it is not dropping the table in a reload.
Maybe a solution to this is to drop the table with the foreign key references (ADDRESS) before you drop STATE and COUNTRY, but then load the STATE and COUNTRY tables first, then ADDRESS.
Maybe a solution to this is to drop the table with the foreign key references (ADDRESS) before you drop STATE and COUNTRY, but then load the STATE and COUNTRY tables first, then ADDRESS.
ASKER
So if I understand correctly, then in my Address table is where I would make the setting changes to and I wouldn't need to set up any cascades in the state or country table at all:
state_id which references states->state_id
ON DELETE CASCADE ON UPDATE CASCADE
and
iso which references country->iso
ON DELETE CASCADE ON UPDATE CASCADE
So is it generally a good idea to set everything to cascade in the child tables?
Also, you guys are fast. I have had a mysql/Perl question posted in the Perl section for like 3-4 days without a single response. If any of you guys use mysql with perl maybe you can check that out too.