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');
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
