MySQL ODBC Connector - referential integrity lock up

Author

referential integrity lock up

Enrique Sanchez Vela

2005-09-29, 3:23 am

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...sie.nctu.edu.tw

Enrique Sanchez Vela

2005-09-29, 3:23 am

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:
>
> script
> key
> command
>
-----------------------------------------------------------------------------
> live
> Kay
>
http://lists.mysql.com/mysql? unsub...ahoo
.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...sie.nctu.edu.tw

Sponsored Links