Manage Relations – Delete

[advantis]

I have these two tables:

Code:

CREATE TABLE `Test1` (
         `PK_Test1` int(10) unsigned NOT NULL auto_increment,
         `FK_Test2` int(10) unsigned default NULL,
         PRIMARY KEY  (`PK_Test1`),
         KEY `FK_Test2` (`FK_Test2`),
         CONSTRAINT `Test1_ibfk_1` FOREIGN KEY (`FK_Test2`) REFERENCES `Test2` (`PK_Test2`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1

CREATE TABLE `Test2` (
         `PK_Test2` int(10) unsigned NOT NULL auto_increment,
         `Data` varchar(255) default NULL,
         PRIMARY KEY  (`PK_Test2`)
       ) ENGINE=InnoDB DEFAULT CHARSET=latin1

When I want to drop the foreign key in Test1, SQLyog 4.0.4 Free (4.0 even gave an SQL error) does this:

Code:

/*[1:10:23 PM][  31 ms]*/ create temporary table if not exists `test`.`sqlyog_2238` ( `PK_Test1` int(10) unsigned NOT NULL auto_increment , `FK_Test2` int(10) unsigned NULL  ,  primary key ( PK_Test1 ) ,KEY `FK_Test2` (  `FK_Test2` ))
/*[1:10:24 PM][  78 ms]*/ lock tables `test`.`Test1` read
/*[1:10:24 PM][  32 ms]*/ insert into `test`.`sqlyog_2238` select `PK_Test1`, `FK_Test2` from `test`.`Test1`
/*[1:10:24 PM][   0 ms]*/ unlock tables
/*[1:10:24 PM][  46 ms]*/ select count(*) from `test`.`Test1`
/*[1:10:24 PM][   0 ms]*/ select count(*) from `test`.`sqlyog_2238`
/*[1:10:24 PM][  16 ms]*/ drop table `test`.`Test1`
/*[1:10:24 PM][  47 ms]*/ create table `test`.`Test1` ( `PK_Test1` int(10) unsigned NOT NULL auto_increment , `FK_Test2` int(10) unsigned NULL  ,  primary key ( PK_Test1 ) ,KEY `FK_Test2` (  `FK_Test2` ))Type=InnoDB
/*[1:10:24 PM][   0 ms]*/ insert into `test`.`Test1` select * from `test`.`sqlyog_2238`
/*[1:10:24 PM][   0 ms]*/ select count(*) from `test`.`Test1`
/*[1:10:24 PM][   0 ms]*/ select count(*) from `test`.`sqlyog_2238`
/*[1:10:24 PM][  47 ms]*/ drop table `test`.`sqlyog_2238`

I can see two problems in that transcript. One is about creating a temporary table (the one that stores my data to be inserted back into my real table after reconstruction) “if not exists”, so if it does exist I guess I'd get supplemental data that wasn't there before… I guess something like “DROP TABLE IF EXISTS” would be safe.

Second, at least MySQL 4.1 knows about this, which is safer and needs no warning 11 point warning from SQLyog:

Code:

ALTER TABLE Test1 DROP FOREIGN KEY `Test1_ibfk_1`

I'm not too experienced with SQL, but what I describe here works for me, so tell me if there's something wrong in what I say and I'm missing it.

[Ritesh]

Quote:

I can see two problems in that transcript. One is about creating a temporary table (the one that stores my data to be inserted back into my real table after reconstruction) “if not exists”, so if it does exist I guess I'd get supplemental data that wasn't there before… I guess something like “DROP TABLE IF EXISTS” would be safe.

We use if not exists so that we dont delete an existing table (which might be important) by mistake.

Quote:

Second, at least MySQL 4.1 knows about this, which is safer and needs no warning 11 point warning from SQLyog:

SQLyog v4.1 BETA actually checks for MySQL version and uses the correct method. For e.g. if MySQL version is 4.1.x then it will use ALTER TABLE Test1 DROP FOREIGN KEY `Test1_ibfk_1` statement. Otherwise it will go for the original 11 steps method to drop the FK.

[Author]

Posts