View original (French)

[SQL] table deletion - parent-child

Solved
azerty0 Posted messages 1270 Registration date   Status Member Last intervention   -  
azerty0 Posted messages 1270 Registration date   Status Member Last intervention   -
Hello,

I resolved some issues with my integrity constraints yesterday, but now I can’t delete tables due to these constraints. Is it possible to delete a parent table nevertheless???

Here is the code: 

 DROP TABLE IF EXISTS 'date'; CREATE TABLE 'date' ( 'id_date' int(10) unsigned NOT NULL auto_increment, 'formate' date NOT NULL , 'jour' varchar(8) default NULL, INDEX formate_ind ('formate'), CONSTRAINT pk_date PRIMARY KEY ('id_date') ) ENGINE =InnoDB DEFAULT CHARSET=utf8 ; -- -- Structure of the 'entree' table -- An entry corresponds to a time slot (whole hours) -- DROP TABLE IF EXISTS 'entree'; CREATE TABLE 'entree' ( 'ent_id' int(10) unsigned NOT NULL auto_increment , 'ent_id_date' date NOT NULL , 'ent_heure_deb' int(2) NOT NULL, 'ent_heure_fin' int(2) NOT NULL, CONSTRAINT pk_entree PRIMARY KEY ('ent_id'), INDEX ent_id_ind ('ent_id'), CONSTRAINT fk_entree_date FOREIGN KEY ('ent_id_date') REFERENCES 'date' ('formate') ON DELETE CASCADE ) ENGINE =InnoDB DEFAULT CHARSET=utf8 ;


I can’t delete the date table, phpmyadmin gives me this error: 

#1217 - Cannot delete a parent record: a foreign key constraint prevents it


I tried to drop the constraint from the entree table just before the DROP of the date table: 

ALTER TABLE entree DROP CONSTRAINT 'fk_entree_date' ;


But it tells me there is a syntax error… =/

3 answers

Answer 1 / 3
azerty0 Posted messages 1270 Registration date   Status Member Last intervention   75
 
To maintain referential integrity, it is impossible to delete child tables, so I disabled foreign keys, created my tables, and re-enabled the keys:

SET FOREIGN_KEY_CHECKS=0;

DROP IF EXISTS
CREATE

SET FOREIGN_KEY_CHECKS=1;
2