CODEWITHSUNDEEP
mysql
user3796869
user3796869

Reputation: 241

SET FOREIGN_KEY_CHECKS=0 inside mysql procedure

I have two tables which are having PK and FK relationship as shown below.

QUERY

CREATE TABLE `sector_tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`code` smallint(6) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `code` (`code`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8;

CREATE TABLE `ticker_tab` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`company_title` varchar(255) DEFAULT NULL,
`sector_num` smallint(6) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `ind_sector_number` (`sector_num`),
CONSTRAINT `ticker_tab_ibfk_1` FOREIGN KEY (`sector_num`) 
REFERENCES `sector_tab` (`code`),
) ENGINE=InnoDB AUTO_INCREMENT=4096 DEFAULT CHARSET=utf8;

Now when i want to truncate 'sector_tab' table but because of PK-FK relationship i am getting error, so I want to disable it with SET FOREIGN_KEY_CHECKS=0 but i am not able to do but when i mentioned FOREIGN_KEY_CHECKS=0 inside procedure as shown below and running it its working. How come this happends?

CREATE PROCEDURE `proc_FK_diable`()<br>
BEGIN

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE TABLE sector_tab;
SET FOREIGN_KEY_CHECKS=1;<br>
END

I am doing from Mysql Query Browser 1.1

Upvotes: 3

Views: 3131

Answers (1)

Cristian Fritz
Cristian Fritz

Reputation: 11

delimiter $
CREATE PROCEDURE proc_FK_diable()
BEGIN

SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE sector_tab;

SET FOREIGN_KEY_CHECKS = 1;
END
$

Upvotes: 1

Related Questions