Skip site navigation (1) Skip section navigation (2)

Foreign Key/ALTER TABLE Issue

From: "Rao Kumar" <raokumar(at)netwolves(dot)com>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Foreign Key/ALTER TABLE Issue
Date: 2002-06-25 19:08:13
Message-ID: NBEEJBHFGDGFKNKHGGAFOEMGCCAA.raokumar@netwolves.com (view raw or flat)
Thread:
Lists: pgsql-hackers
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');










Responses

pgsql-hackers by date

Next:From: Josh BerkusDate: 2002-06-25 19:30:12
Subject: Re: Democracy and organisation : let's make a revolution in
Previous:From: Greg Sabino MullaneDate: 2002-06-25 19:03:39
Subject: Postgres idea list

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group