Tripp, thanks for your prompt response, however I would like to keep the FOREIGN_KEY_CHECKS enabled so I dont shoot myself on the foot.
unless, as it looks right now, it turns impossible to delete stuff from the table. thanks, esv. --- Tripp Bishop <[EMAIL PROTECTED]> wrote: > Enrique, > > Try this... > > SET FOREIGN_KEY_CHECKS = 0; > > DELETE FROM table WHERE ... > > SET FOREIGN_KEY_CHECKS = 1; > > You can temporarily suspend FK constraint checking > with the first line. This works create if you have > to > restore a INNODB database from a mysqldump script. > > Cheers, > > Tripp > --- Enrique Sanchez Vela <[EMAIL PROTECTED]> > wrote: > > > hi, > > > > I've been assigned the task to come up with a > script > > to clean up a database that has a LOT of foreign > key > > constraints, where tables make reference to each > > other > > and back, some of the tables are empty or have a > > record or so and make the process a breze but I > > wonder > > what would happen the day they fully populate the > > database and then run into issues. > > > > As an example, here is the show create table > command > > from one of them... > > > > =================================== > > > > CREATE TABLE `show_cat` ( > > `id` int(11) NOT NULL auto_increment, > > `parent_id` int(11) default NULL, > > `dataset` varchar(30) default 'any', > > `name` varchar(200) default NULL, > > `description` text, > > `publish` char(1) default 'Y', > > PRIMARY KEY (`id`), > > KEY `parent_id` (`parent_id`), > > CONSTRAINT `show_cat_ibfk_1` FOREIGN KEY > > (`parent_id`) REFERENCES `show_cat` (`id`) > > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > > > =================================== > > > > I would like to know what would be the best method > > to > > delete some records of the table without lossing > > referential integrity. > > > > thanks, > > esv. > > > > > > > > > > > > > > Enrique Sanchez Vela > > email: [EMAIL PROTECTED] > > > ----------------------------------------------------------------------------- > > It's often easier to fight for one's || We > live > > in the outer space > > principles than to live up to them || Rev. > Kay > > Greenleaf > > Adlai Stevenson || > > > > > > > > __________________________________ > > Yahoo! Mail - PC Magazine Editors' Choice 2005 > > http://mail.yahoo.com > > > > -- > > MySQL General Mailing List > > For list archives: http://lists.mysql.com/mysql > > To unsubscribe: > > > http://lists.mysql.com/[EMAIL PROTECTED] > > > > > > > > > __________________________________ > Yahoo! Mail - PC Magazine Editors' Choice 2005 > http://mail.yahoo.com > Enrique Sanchez Vela email: [EMAIL PROTECTED] ----------------------------------------------------------------------------- It's often easier to fight for one's || We live in the outer space principles than to live up to them || Rev. Kay Greenleaf Adlai Stevenson || __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
