I was looking through my database schema and noticed that some foreign key
declarations were duplicated. For example, I found this statement
occurred twice within my DDL:
alter table ports
add foreign key (category_id)
references categories (id) on update cascade on delete cascade;
I fixed up the DDL to remove the duplicates. Then I turned to the 7.2
PostgreSQL database to examine that situation. I noticed this
duplication:
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'ports', 'categories', 'UNSPECIFIED', 'category_id', 'id');
CREATE CONSTRAINT TRIGGER "<unnamed>" AFTER INSERT OR UPDATE ON "ports"
FROM "categories" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins"
('<unnamed>', 'ports', 'categories', 'UNSPECIFIED', 'category_id', 'id');
To my eye, those two contraints are identical. Some may consider that as
a bug (i.e postgres allowing a FK to be duplicated). I see it as a
feature, which in this case, has been misused.
>From what I see, I can easily delete either one of these two constraints
without affecting the relational integrity of the database. Do you agree?
This is how I'm going to identify the duplicate constraints.
$ pg_dump -s > fp2migration.ddl
$ grep "CREATE CONSTRAINT TRIGGER" ~/fp2migration.ddl > constraints.txt
$ cat constraints.txt | sort | uniq > constraints.sorted.txt
$ wc -l constraints.txt
93 constraints.txt
$ wc -l constraints.sorted.txt
78 constraints.sorted.txt
It looks like I have 15 duplicated constraints to fix up.
Thanks
--
Dan Langille
The FreeBSD Diary - http://freebsddiary.org/ - practical examples
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
