13/02/2015
Attaque des clones
J'ai hérité au boulot d'une base de données sans la moindre contrainte d'intégrité (unicité, valeurs par défaut, clés étrangères, valeurs exclusives... rien), donc j'ai du les ajouter, donc j'ai du commencer par nettoyer les données aberrantes qui ne respectaient pas ces nouvelles contraintes.
Nettoyer les clés étrangères ? Facile, une contrainte comme
alter table `tableA` add constraint `tableA_fk_tableB` foreign key (`tblA_keyA`) references `tableB` (`tblB_keyA`) on update cascade on delete set null;
implique quelque chose comme
update `tableA` set `tblA_keyA` = NULL where `tblA_keyA` not in (select `tblB_keyA` from `tableB`);
ou en version plus stricte avec suppression en cascade :
alter table `tableA` add constraint `tableA_fk_tableB` foreign key (`tblA_keyA`) references `tableB` (`tblB_keyA`) on update cascade on delete cascade;
qu'on précèdera d'un beau nettoyage par le vide
delete from `tableA` where `tblA_keyA` not in (select `tblB_keyA` from `tableB`);
Simple et intuitif, beaucoup moins quand on ajoute une règle d'unicité et que pour chaque ensemble de doublons trouvés on veut quand même garder le premier :
alter table `tableA` add constraint `tableA_unique_composite_key` unique (`keyA`, `keyB`, `keyC`);
Allez, on s'épargne une hausse peu raisonnable de tension artérielle, et du temps perdu :
delete from `tableA`
where id in (select id from (select tbl.id from
`tabelA` tbl,
(select id, name, keyA, keyB, keyC, count(*) from `tableA` group by keyA, keyB, keyC having count(*) > 1) dupes
where
tbl.id <> dupes.id and
((tbl.keyA = dupes.keyA) or (tbl.keyA is null and dupes.keyA is null)) and
((tbl.keyB = dupes.keyB) or (tbl.keyB is null and dupes.keyB is null)) and
((tbl.keyC = dupes.keyC) or (tbl.keyC is null and dupes.keyC is null))
) toremove);
Enjoy.