Re: Table constraints

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.