Message-ID: <3CA37F3B.25CF3F21@waterford.org>
Date: Thu, 28 Mar 2002 13:38:19 -0700
From: Oleg Lebedev <olebedev@waterford.org>
MIME-Version: 1.0
To: Postgres SQL Mailing List <pgsql-sql@postgresql.org>
Subject: Re: Table constraints
References: <20020328110701.E25475-100000@megazone23.bigpanda.com>
Content-Type: text/plain; charset=koi8-r
Content-Transfer-Encoding: 7bit

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?