From: Oleg Lebedev <olebedev@waterford.org>
To: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Table constraints
Date: Thu, 28 Mar 2002 13:38:19 -0700
Message-ID: <3CA37F3B.25CF3F21@waterford.org> (raw)
References: <20020328110701.E25475-100000@megazone23.bigpanda.com>
Stephan,
of course you were right about the number of created triggers. I must have
counted them wrong.
Here is what I do (in my previous example type = activitytype, id = objectid)
webspectest=# select count(*) from pg_trigger;
count
-------
119
(1 row)
webspectest=# alter table "set" add constraint "fk_acttype" foreign key
(acttype) references activitytype (objectid) on update cascade;
NOTICE: ALTER TABLE ... ADD CONSTRAINT will create implicit trigger(s)
for FOREIGN KEY check(s)
CREATE
webspectest=# select count(*) from pg_trigger;
count
-------
122
(1 row)
webspectest=# update activitytype set objectid=999 where objectid=1;
UPDATE 1
webspectest=# alter table set drop constraint fk_acttype;
ERROR: parser: parse error at or near ";"
So the only question left is how do I drop this constraint now?
BTW, could you briefly explain to me what kind of triggers were created for
this constraint.
thanks,
Oleg
Stephan Szabo wrote:
> On Thu, 28 Mar 2002, Oleg Lebedev wrote:
>
> > Hi everybody,
> > I saw a couple of messages regarding rule/constraint/trigger standards
> > which Tom proposed to adopt in postgres. I've read through the current
> > specs, but still can't figure it out. I am using version 7.1.3 and this
> > is what I am trying to do:
> > I have 2 tables:
> > Set { type_id int,
> > set_desc varchar(128) }
> > Type { id int primary key }
> >
> > I want to update a row in Type table and cascade this update to update
> > Set table. I declare a constraint as follows:
> > ALTER TABLE Set
> > ADD CONSTRAINT fk_type
> > FOREIGN KEY (type_id)
> > REFERENCES Type (id)
> > ON UPDATE CASCADE;
> > Postgres gives me a NOTICE and creates the constraint.
> >
> > Here are some questions:
> > Why pg_relcheck table is still empty after the constraint is added?
>
> relcheck is only for CHECK constraints.
>
> > Why instead it created 5 triggers (I checked pg_class.reltriggers for
> > Set table): 3 called "fk_type" on Set and 2 unnamed on Type?
>
> Hmm, it should have only created 3 triggers, 1 on set and 2 on Type
> and they should have all had tgconstrname set to fk_type.
> What does select * from pg_trigger say?
>
> > Why when I try to update id in Type table I get RI violation error?
> > Shouldn't it cascade the update?
>
> It works for me in 7.2. I don't have 7.1.3 to test against right now
> but I'm pretty sure that's in the regression test.
>
> Can you give a short script that illustrates the problem?
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: olebedev@waterford.org
Subject: Re: Table constraints
In-Reply-To: <3CA37F3B.25CF3F21@waterford.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox