Thread: Table constraints
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?
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?
Why when I try to update id in Type table I get RI violation error?
Shouldn't it cascade the update?
How can I drop the constraint now? I set reltriggers to 0 in pg_class
entry corresponding to Set table, and tried to delete generated
triggers, but since only 3 out of 5 have "fk_type" name, I couldn't
figure out how to track down the rest of them.
Thanks in advance for any help.
Oleg
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?
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?
On Thu, 28 Mar 2002, Oleg Lebedev wrote: > 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? Use drop trigger on the three triggers it created. You'll need to double quote the trigger names (they're mixed case and not the constraint name that you gave but an internal name.) > BTW, could you briefly explain to me what kind of triggers were created for > this constraint. I'll give the short view, there's a document on it on techdocs that goes into more detail. There are three triggers, one on the referencing (fk) table, two on the referenced (pk) table. The trigger on the referencing table fires after insert or update to that table and makes sure that an appropriate row exists in the pk table. One of the triggers on the referenced table fires after update to that table and calls one of a set of function based on whether a referential action was asked for and if so which one. The final trigger fires after delete to the referenced table and handles delete actions.