miércoles, 29 marzo 2006
Chequear integridad referencial en MySQL
He encontrado esta maravilla de script.
Sirve para chequear la integridad referencial en todas las tablas de una base de datos.
La url origina: En la web del Mysql
El script modificado para que funcione con el MySQL 4.1:
#!/bin/sh # check_constraints.sh # -------------------- # Check foreign key contraints on MySQL database. # # Written by Frank Vanderhallen, licensed under GPL. if [ -z "$1" ] then echo "Usage: ./`uname $0`[-h ] [-u user] [-p ]\n" exit fi CONSTRAINTS=`mysqldump $* | grep "CREATE\|CONSTRAINT" | sed 's/ /+/g'` for c in $CONSTRAINTS do if [ "`echo $c | cut -d '+' -f 3`" = "CONSTRAINT" ] then CONSTRAINT=`echo $c | cut -d '+' -f 4 | tr -d '\`'` CHILD_KEY=`echo $c | cut -d '+' -f 7 | tr -d '()\`,'` PARENT_TABLE=`echo $c | cut -d '+' -f 9 | tr -d '\`'` PARENT_KEY=`echo $c | cut -d '+' -f 10 | tr -d '()\`,'` QUERY="select c.$CHILD_KEY from $CHILD_TABLE as c left join $PARENT_TABLE as p on p.$PARENT_KEY=c.$CHILD_KEY where c.$CHILD_KEY is not null and p.$PARENT_KEY is null;" echo "Checking table '$CHILD_TABLE' constraint '$CONSTRAINT'" mysql $* -e "$QUERY" else CHILD_TABLE=`echo $c | cut -d '+' -f 3` fi done
Posted by at 6:24 PM in Linux
