Thread: removing duplicated constraints
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
On 2 Mar 2002 at 10:19, Dan Langille wrote: > 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'); How is an unnamed trigger deleted? During a dump, I can find information such as this: -- -- TOC Entry ID 211 (OID 998200) -- -- Name: "RI_ConstraintTrigger_998199" Type: TRIGGER Owner: dan -- How is that information useful to this situation? -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples
On Sat, 2 Mar 2002, Dan Langille wrote: > On 2 Mar 2002 at 10:19, Dan Langille wrote: > > > 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'); > > How is an unnamed trigger deleted? During a dump, I can find information > such as this: The "<unnamed>" is the constraint name given. It also gets a trigger name (see system table pg_trigger) which you can use with DROP TRIGGER (although in the case of the fk constraint triggers you need to double quote the name).
On 2 Mar 2002 at 11:09, Stephan Szabo wrote: > On Sat, 2 Mar 2002, Dan Langille wrote: > > > On 2 Mar 2002 at 10:19, Dan Langille wrote: > > > > > 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'); > > > > How is an unnamed trigger deleted? During a dump, I can find information > > such as this: > > The "<unnamed>" is the constraint name given. It also gets a trigger name > (see system table pg_trigger) which you can use with DROP TRIGGER (although > in the case of the fk constraint triggers you need to double quote the > name). I found the list of duplicate constraints by eyeballing the files I created using the methods mentioned in previous post (mainly grep and sort). Then I would search in the original dump file for the the CREATE CONSTRAINT statement. Using the name found there (RI_ConstraintTrigger_998199), I did this: drop TRIGGER "RI_ConstraintTrigger_998189" on categories; Making sense? -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples
> I found the list of duplicate constraints by eyeballing the files I > created using the methods mentioned in previous post (mainly grep and > sort). Then I would search in the original dump file for the the CREATE > CONSTRAINT statement. Using the name found there > (RI_ConstraintTrigger_998199), I did this: > > drop TRIGGER "RI_ConstraintTrigger_998189" on categories; > > Making sense? Well, I'm not sure that you're guaranteed to get the same trigger names after the restore as the ones in the dump file comments. You may have to look in the system table for the actual current trigger name for the triggers with those arguments.
On 2 Mar 2002 at 23:42, Stephan Szabo wrote: > > > I found the list of duplicate constraints by eyeballing the files I > > created using the methods mentioned in previous post (mainly grep and > > sort). Then I would search in the original dump file for the the CREATE > > CONSTRAINT statement. Using the name found there > > (RI_ConstraintTrigger_998199), I did this: > > > > drop TRIGGER "RI_ConstraintTrigger_998189" on categories; > > > > Making sense? > > Well, I'm not sure that you're guaranteed to get the same trigger names > after the restore as the ones in the dump file comments. You may have to > look in the system table for the actual current trigger name for the > triggers with those arguments. There was no restore. I did the dump in order to find the duplicates. Then used that information to find and remove them. cheers -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples
On Sun, 3 Mar 2002, Dan Langille wrote: > On 2 Mar 2002 at 23:42, Stephan Szabo wrote: > > > > > > I found the list of duplicate constraints by eyeballing the files I > > > created using the methods mentioned in previous post (mainly grep and > > > sort). Then I would search in the original dump file for the the CREATE > > > CONSTRAINT statement. Using the name found there > > > (RI_ConstraintTrigger_998199), I did this: > > > > > > drop TRIGGER "RI_ConstraintTrigger_998189" on categories; > > > > > > Making sense? > > > > Well, I'm not sure that you're guaranteed to get the same trigger names > > after the restore as the ones in the dump file comments. You may have to > > look in the system table for the actual current trigger name for the > > triggers with those arguments. > > There was no restore. I did the dump in order to find the duplicates. > Then used that information to find and remove them. Then yes, that's all you should need to do.