CODEWITHSUNDEEP
mysqlforeign-keys
RajSanpui
RajSanpui

Reputation: 12094

set foreign_key_checks=0 does not work MySQL 5.0

We don't support InnoDB databases. Hence before migrating to MySQL 5.5 from 5.0, I wish to change all InnoDB tables to MyISAM, but lot of them have foreign key constraints.

Hence I thought to disable it on a session level executing set foreign_key_checks=0 but still get the same error for foreign key.

mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> use sn;
Database changed
mysql> alter table facility engine='MyISAM';
ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails

Surprisingly:

mysql> SHOW Variables WHERE Variable_name='foreign_key_checks';
Empty set (0.00 sec)

mysql> SET foreign_key_checks = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW Variables WHERE Variable_name='foreign_key_checks';
Empty set (0.00 sec)

The MySQL 5.0 documentation shows the variable indeed exists.

Here is the table structure of `facility` table:
| facility | CREATE TABLE `facility` (
  `FacilityId` bigint(20) NOT NULL auto_increment,
  `FacilityName` varchar(45) default NULL,
  `ViewerId` bigint(20) default NULL,
  `MessageTemplate` varchar(256) default NULL,
  `RecApp` varchar(60) default NULL,
  `RecFacility` varchar(60) default NULL,
  `SendApp` varchar(60) default NULL,
  `SendFacility` varchar(60) default NULL,
  `MirthChannelEnable` int(1) NOT NULL default '0',
  `CompletenessCriteria` int(2) NOT NULL,
  `RetryLimit` int(2) NOT NULL default '3',
  `WatchDelay` int(11) NOT NULL,
  `RetryDelay` int(11) NOT NULL,
  `AckTimeOut` int(11) NOT NULL,
  `KeepConOpen` int(1) NOT NULL default '0',
  `SendTimeout` int(11) NOT NULL,
  `EncryptURL` int(1) NOT NULL default '0',
  `Host` varchar(15) NOT NULL,
  `Port` int(11) NOT NULL,
  `CreationDate` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `Description` varchar(256) default NULL,
  `EventLogOffset` int(11) NOT NULL default '0',
  PRIMARY KEY  (`FacilityId`),
  KEY `Fk_Fac_ViewerId_idx` (`ViewerId`),
  CONSTRAINT `Fk_Fac_ViewerId` FOREIGN KEY (`ViewerId`) REFERENCES `viewer` (`ViewerId`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Upvotes: 4

Views: 10975

Answers (1)

Shadow
Shadow

Reputation: 34285

As MySQL documentation on foreign key constraint says (highlighting is mine):

Also, if a table has foreign key constraints, ALTER TABLE cannot be used to alter the table to use another storage engine. To change the storage engine, you must drop any foreign key constraints first.

The documentation is from v5.5, but I would assume that this restriction also applies to v5.0 as well.

UPDATE

Yes, I found the v5.0 manual on Oracle's website and the relevant section says the same:

Also, if an InnoDB table has foreign key constraints, ALTER TABLE cannot be used to change the table to use another storage engine. To alter the storage engine, you must drop any foreign key constraints first.

Upvotes: 2

Related Questions