0

I am trying to TRUNCATE some TABLES in my DB in MySQL, but when I try to do it, there is an error.

Please have in count that I've set the FOREIGN_KEY_CHECK to 0, like says in line 2 and returned back to 1 in line 9

Why and how to solve it?

Here my query My SQL Query that desnt works

Here is the error I got The error

Adding tables structure

--
-- Table structure for table `metadata`
--

CREATE TABLE IF NOT EXISTS `metadata` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user` int(11) NOT NULL,
  `date` date NOT NULL,
  `time` time NOT NULL,
  `ip` varchar(15) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user` (`user`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=54 ;

-- --------------------------------------------------------

--
-- Table structure for table `patients`
--

CREATE TABLE IF NOT EXISTS `patients` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `nss` bigint(11) DEFAULT NULL,
  `name_1st` varchar(50) NOT NULL,
  `name_2nd` varchar(30) DEFAULT NULL,
  `last_name_1st` varchar(50) NOT NULL,
  `last_name_2nd` varchar(30) DEFAULT NULL,
  `clinic` int(11) DEFAULT NULL,
  `attached` text,
  `valid` tinyint(1) DEFAULT NULL,
  `status` tinyint(2) NOT NULL,
  `metadata` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `status` (`status`),
  KEY `clinic` (`clinic`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=15 ;

-- --------------------------------------------------------

--
-- Table structure for table `queue`
--

CREATE TABLE IF NOT EXISTS `queue` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `consecutive` int(11) NOT NULL,
  `creat_day` date NOT NULL,
  `patient` bigint(20) DEFAULT NULL,
  `status` tinyint(2) DEFAULT NULL,
  `metadata` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `consecutive` (`consecutive`,`creat_day`),
  KEY `status` (`status`),
  KEY `metadata` (`metadata`),
  KEY `patient_ident` (`patient`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=16 ;

-- --------------------------------------------------------

--
-- Table structure for table `triage_regs`
--

CREATE TABLE IF NOT EXISTS `triage_regs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `queue` bigint(20) NOT NULL,
  `patient` bigint(20) NOT NULL,
  `cod_color` int(11) NOT NULL,
  `medicines` text NOT NULL,
  `time_start_triage` time NOT NULL,
  `time_end_triage` time NOT NULL,
  `tens_arterial` varchar(7) NOT NULL,
  `frec_card` int(3) NOT NULL,
  `frec_resp` int(3) NOT NULL,
  `temp` int(3) NOT NULL,
  `gluc` int(3) NOT NULL,
  `esc_glasgown` int(3) NOT NULL,
  `atention` varchar(500) NOT NULL DEFAULT 'n/a',
  `questions` longtext,
  `exp_fis` longtext,
  `aux_diag` longtext,
  `pron` longtext,
  `trat` longtext,
  `diag` longtext,
  `status` tinyint(2) NOT NULL,
  `metadata` bigint(20) DEFAULT NULL,
  `metadata2` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `patient` (`patient`),
  KEY `status` (`status`),
  KEY `metadata2` (`metadata2`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
Dharman
  • 30,962
  • 25
  • 85
  • 135
Frank Leal
  • 212
  • 4
  • 18

1 Answers1

0

It is exactly as the error says: You cannot truncate a table referenced BY a foreign key constraint, being it a WITH CHECK constraint or not. This is because TRUNCATE forces the table to be dropped and then created again, and obviously a table which is defined as "master" in a foreign key relationship cannot be dropped, even temporarily.

So what you have to do is first drop all FK constraints which reference your table, then TRUNCATE it, then recreate the FK constraints.

Giorgos Altanis
  • 2,742
  • 1
  • 13
  • 14