Hi,
Sometimes i end up with some duplicated constraints definitions in my database,
i've noticed this when i reverse engineer databases, 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 duplicated definitions, 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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
