In order to apply a dependency of foreign keys against a column set
the most obvious way to go is via the unique index which in turn
depends on the expected columns.

A(id) -> B(id)

A.id -> Foreign key -> Index on B.id -> B.id

If B.id is dropped it'll cascade forward.


The trick?  Foreign keys are currently any number of triggers without
a central location  for marking them as such.  So...

A.id
    -> Trigger on A -> Index on B.id -> B.id
    -> Trigger on A -> Index on B.id -> B.id
    -> Trigger on A -> Index on B.id -> B.id

Of course, since Trigger on A depends on A we also have
Trigger on A -> B.id

Not so bad if we can go with the currently coded assumption that
dependencies will be dropped starting with the columns (during DROP
TABLE) and then do the relation.

This will allow dropping tons of stuff via foreign key relations and a
CASCADE option but it won't make them very nice to look at.  Not to
mention the trigger creation code would need knowledge of foreign keys
or more specifically indexes.

Is everyone Ok with the above?  Or do we go about making an pg_fkey
type table for tracking this stuff?

FKey Triggers -> pg_fkey entry
A.id -> pg_fkey entry
pg_fkey entry -> index on B.id -> B.id

Self scrubbing really.  Makes foreign keys really obvious.  Foreign
key code needs to know about triggers, not the other way around.

Of course, this depends on the pg_depend stuff I just submitted to
patches.  Any thoughts on the data pg_fkey would need?  Name, A.oid,
A.<int2 vector -- column list>

--
Rod


Sat, 20 Apr 2002 22:07:18 -0400
"Rod Taylor" <rbt@zort.ca>
Foreign keys and indexes.
> Of course, since Trigger on A depends on A we also have
> Trigger on A -> B.id

Should read:
Trigger on A -> relation A

Triggers depend on relation which owns it :)


Sat, 20 Apr 2002 22:16:17 -0400
"Rod Taylor" <rbt@zort.ca>
Re: Foreign keys and indexes.
"Rod Taylor" <rbt@zort.ca> writes:
> Is everyone Ok with the above?  Or do we go about making an pg_fkey
> type table for tracking this stuff?

In general there ought to be a pg_constraint table that records all
types of constraints (not only foreign keys).  We blew it once already
by making pg_relcheck (which only handles check constraints).  Let's
not miss the boat again.

			regards, tom lane

Sun, 21 Apr 2002 14:26:00 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: Foreign keys and indexes. 