I have noticed that unlike indexes/check constrains, "ALTER TABLE ADD
CONSTRAINT <c_name> FOREIGN KEY ..." statement does NOT prevent a user from
re-creating an existing constraint more than once. Following this, a pg_dump
on the table showed multiple entries of the foreign key constraint/trigger
definitions.

My concerns are:

If it ends up creating multiple triggers (doing the same task), do all these
triggers
get executed for each DML operation ?.
Will this cause a performance hit, if so is there a work-around to
remove duplicate entries from the sys tables ?

-- Rao Kumar

Example: Running Postgres 7.1.3
========
test=# create table emp (emp_id integer NOT NULL PRIMARY KEY, emp_name
varchar(20),dept_id integer);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'emp_pkey' for
table 'emp'
CREATE
test=# create table dept (dept_id integer NOT NULL PRIMARY KEY, dept_name
varchar(20));
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'dept_pkey' for
table 'dept'
CREATE
test=# alter table emp add constraint fk_emp_dept_id foreign key (dept_id)
references dept (dept_id);
NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
--- TRY CREATING THE KEY AGAIN .........
test=# alter table emp add constraint fk_emp_dept_id foreign key (dept_id)
references dept (dept_id);
NOTICE:  ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
test=#
pg_dump of "emp" table.
======================
-- Selected TOC Entries:
--
\connect - raokumar
--
-- TOC Entry ID 2 (OID 53485)
--
-- Name: emp Type: TABLE Owner: raokumar
--

CREATE TABLE "emp" (
        "emp_id" integer NOT NULL,
        "emp_name" character varying(20),
        "dept_id" integer,
        Constraint "emp_pkey" Primary Key ("emp_id")
);

--
-- Data for TOC Entry ID 3 (OID 53485)
--
-- Name: emp Type: TABLE DATA Owner: raokumar
--


COPY "emp"  FROM stdin;
\.
--
-- TOC Entry ID 5 (OID 53515)
--
-- Name: "RI_ConstraintTrigger_53514" Type: TRIGGER Owner: raokumar
--

CREATE CONSTRAINT TRIGGER "fk_emp_dept_id" AFTER INSERT OR UPDATE ON "emp"
FROM "dept" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins" ('fk_emp_dept_id', 'emp', 'dept',
'UNSPECIFIED', 'dept_id', 'dept_id');

--
-- TOC Entry ID 4 (OID 53521)
--
-- Name: "RI_ConstraintTrigger_53520" Type: TRIGGER Owner: raokumar
--

CREATE CONSTRAINT TRIGGER "fk_emp_dept_id" AFTER INSERT OR UPDATE ON "emp"
FROM "dept" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins" ('fk_emp_dept_id', 'emp', 'dept',
'UNSPECIFIED', 'dept_id', 'dept_id');










Tue, 25 Jun 2002 15:08:13 -0400
"Rao Kumar" <raokumar@netwolves.com>
Foreign Key/ALTER TABLE Issue
On Tue, 25 Jun 2002, Rao Kumar wrote:

> I have noticed that unlike indexes/check constrains, "ALTER TABLE ADD
> CONSTRAINT <c_name> FOREIGN KEY ..." statement does NOT prevent a user from
> re-creating an existing constraint more than once. Following this, a pg_dump
> on the table showed multiple entries of the foreign key constraint/trigger
> definitions.

Correct.  The assumption is that the user knows what he or she is doing
(and thus that the constraints are different in some way).  We might want
to change this at some point, but this isn't only foreign keys (you can
do the same with unique indexes or check constraints afaik) and should
probably be dealt with as such.

> My concerns are:
>
> If it ends up creating multiple triggers (doing the same task), do all these
> triggers get executed for each DML operation ?.

Yes.

> Will this cause a performance hit, if so is there a work-around to
> remove duplicate entries from the sys tables ?

You can remove one set of the triggers from pg_trigger which is pretty
much the only way right now to drop a foreign key constraint.




Tue, 25 Jun 2002 14:19:05 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Foreign Key/ALTER TABLE Issue