[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: referential integrity lock up
From:       Enrique Sanchez Vela <esanchezvela () yahoo ! com>
Date:       2005-09-29 4:24:40
Message-ID: 20050929042441.91587.qmail () web32103 ! mail ! mud ! yahoo ! com
[Download RAW message or body]

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 <dyne_erg@yahoo.com> 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 <esanchezvela@yahoo.com>
> 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: esanchezvela@yahoo.com
> >
>
-----------------------------------------------------------------------------
> > 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/mysql?unsub=dyne_erg@yahoo.com
> > 
> > 
> 
> 
> 
> 		
> __________________________________ 
> Yahoo! Mail - PC Magazine Editors' Choice 2005 
> http://mail.yahoo.com
> 


Enrique Sanchez Vela
email: esanchezvela@yahoo.com
-----------------------------------------------------------------------------
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/mysql?unsub=mysql@progressive-comp.com

[prev in list] [next in list] [prev in thread] [next in thread]