Hi all, I've just installed mysql 4.0.1 on my pc and I'm trying to migrate a set of old MyISAM table to InnoDB type table, to introduce referential integrity on my data.
I found this problem. I create an index for each key in the references and the sql command ALTER TABLE utenti ADD CONSTRAINT FOREIGN KEY (iddipartimento) REFERENCES dipartimenti(iddipartimento) ON DELETE RESTRICT ON UPDATE CASCADE; goes through correctly. But trying to display the 'create table' of table 'utenti', it seems that the ON DELETE RESTRICT had been ignored, giving me this output: | utenti | CREATE TABLE `utenti` ( `idutente` int(4) NOT NULL auto_increment, `username` varchar(30) default NULL, `password` varchar(30) default NULL, `realname` varchar(100) default NULL, `attivo` int(1) default '1', `data` date default '2003-06-01', `flag_respons` int(1) default NULL, `iddipartimento` int(4) NOT NULL default '0', PRIMARY KEY (`idutente`), KEY `iddipartimento` (`iddipartimento`), CONSTRAINT `0_155` FOREIGN KEY (`iddipartimento`) REFERENCES `dipartimenti` (`iddipartimento`) ON UPDATE CASCADE ) TYPE=InnoDB | reporting only the 'ON UPDATE CASCADE' directive. Finally, trying to specify only the 'ON DELETE RESTRICT' directive, no directive are displayed on the 'create table', showing once again that this specific directive is ignored. Nevertheless, the reference integrity works fine, beacuse I cannot DELETE a row referenced by another table: trying to do it, mysql> delete from dipartimenti where iddipartimento=1; it results a ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails error. Anyone has an idea about the reason of this behavior? Thanks in advance. AVE ATQUE VALE Marco Barbato ---------------------------------------------------- Tutto ciò che non viene donato andrà perduto. (proverbio indiano) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
