Re: foreign keys - Mailing list pgsql-general

On Mon, 7 Aug 2000, Radoslaw Stachowiak wrote:

> *** Stephan Szabo <sszabo@megazone23.bigpanda.com> [Sunday, 06.August.2000, 14:26 -0700]:
> >
> > Actually, you should only be seeing one constraint out on the referencing
> > table and two out of the referenced one, but yes, fundamentally it only is
> > dumping the constraint triggers for the table you are dumping at the
> > moment.
>
> ok, but let's talk about number of constraints. I think that the correct
> number (for my meaning of full foreign key) is 4. 2 for both tables:
> referenced: UPDATE contrains AND DELETE constraint
> referencing: INSERT constraint AND UPDATE constraint
>
> am I right?

It's actually just one constraint, but it's implemented in 3 triggers,
because one constraint trigger is both INSERT AND UPDATE on
referencing both. The semantics of UPDATE and DELETE on referenced can be
different, so they get separate constraint triggers , but INSERT and
UPDATE on referencing have the same semantics so they share one constraint
trigger that is called on either operation.


> what i need/try to accomplish is to full dump/recreate/modify of table with all
> needed (applied) constraints. Primiary I thought only about referencing
> table but now I know that "prescription" should also mention operation on
> referenced table. In short:
> 1. how to full duplicate/modify table (referencing)
> 2. how to full duplicate/modify table (referenced)
>
> It should take care of fact that it should at start DESTROY table and all
> constraints (on both tables!!!) and than recreate it from scratch - this
> is needed to satisfy the modify case (someone may need to changee FK
> schema to sth different).

Umm, it's very hard to do automatically.  Pretty much, your best bet is to
look at pg_trigger and find constraints that reference the tables you're
doing, dumping the table schema, dropping the table, removing the create
constraint trigger statements that are dumped and replacing them with an
ALTER TABLE ADD CONSTRAINT.


> How can I manipulate existing unnamed (created automaticly by foreign key)
> constraints on tables in PSQL tool ?
Don't use unnamed constraints?  :-)
In practice it's probably always good form to use:
constraint <name> references ... or
constraint <name> foreign key (...) references ...

Seriously, for foreign key constraints, you can remove them by removing
the rows in pg_trigger that are associated with them.  If they're unnamed,
you'll have to use the data in tgargs to determine which is the correct
one.

---
Reading pg_trigger for fk constraints

The function that is referenced will tell you which trigger it is...
You'll need to do something like
select pg_trigger.*, proname from pg_trigger, pg_proc where
pg_trigger.tgfoid=pg_proc.oid.
And the proname will be like
RI_<thing to do>_<ins|del|upd>
The checking constraint on the referencing table is
RI_check_ins I believe.  The constraints on the referenced table will
have the action you specified in the name, so RI_cascade_upd or
RI_setnull_del.

Tgargs stores the information on the tables and columns referenced.
It's in the form:
name\000referencing table\000referenced table\000match type\000
referencing column1\000referenced column 1\000...
(Note: the internal form here may change for 7.1)