Hi,
Sometimes i end up with some duplicated constraints definitions in my database, i've noticed this when i reverse
engineerdatabases, and see many links between two tables.
I prepared a perl script that read an schema on standard input, and prints on standard output some drop constraints for
duplicateddefinitions, if you like you can then execute them against your database:
For example if you save it as drop-dup-constraints.pl, then you can check your database with:
pg_dump -Ox -s mydatabase | drop-dup-constraints.pl
#!/usr/bin/perl
use strict;
use warnings;
## Elimina los constraints sobre el mismo campo y la misma tabla
my ($table, $constName, $field);
my %tuplas;
my @lines = ();
while (<>) {
next if /--/;
chomp;
push @lines, $_;
# Ensamblar el sql acumulado e imprimir
if (/;/) {
&processLine(join " ", @lines);
@lines = ();
}
}
# Results
TUPLA: while (my ($k,$v) = each %tuplas) {
next TUPLA if @$v == 1;
# print "$k\n";
my @arr = @$v;
shift @arr; # let the first constraint
my ($table) = split /,/, $k;
foreach my $constraint (@arr) {
# print "\t$_\n";
printf "alter table %s drop constraint %s;\n", $table, $constraint;
}
}
exit 0;
my $lastSchema = "public";
sub processLine {
local $_ = shift;
chomp;
$lastSchema = $1 if /SET search_path = (\w+)/;
if (my ($table, $constName, $constraintType, $constraintName) = /ALTER TABLE ONLY (\w+)\s+ADD CONSTRAINT (\w+) (FOREIGN
KEY|UNIQUE)\s*\((\w+)\)/){
my $key = "$lastSchema.$table,$constraintType.$constraintName";
my $aref = $tuplas{$key};
unless ($aref) {
$aref = [];
$tuplas{$key} = $aref;
}
push @$aref, $constName;
}
}
Bye
Hans
Hans Poo, Welinux S.A.
Bombero Ossa #1010, oficina 526,
+56-2-3729770, Movil: +56-9-3199305
Santiago, Chile
