Re: foreign keys not working

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Jeff Wright
> Sent: Tuesday, May 14, 2002 12:22 PM
> To: 'pgsql-general@postgresql.org'
> Subject: [GENERAL] foreign keys not working
>
> I'm trying to set up some fk's on some tables and the sql works with no
> errors but the schema display for the table doesn't show the
> fk's. Is there
> something I need to do to enable fk functionality?

Jeff --

Unfortunately, you don't see the FK's indicated on the schema displays in
psql. In the latest versions of PG, you'll see Triggers listed (which
referential integrity is, a trigger), but that still doesn't tell you which
field is the foreign key to which table.

You can see this information by hacking into the referential integrity
tables yourself.

Try this view:

CREATE VIEW dev_ri
AS
SELECT t.oid as trigoid,
    c.relname as trig_tbl,
    t.tgfoid,
    f.proname as trigfunc,
    t.tgenabled,
    t.tgconstrname,
    c2.relname as const_tbl,
    t.tgdeferrable,
    t.tginitdeferred
    FROM pg_trigger t,
    pg_class c,
    pg_class c2,
    pg_proc f
    WHERE t.tgrelid=c.oid
    AND t.tgconstrrelid=c2.oid
    AND tgfoid=f.oid
    AND tgname ~ '^RI_'
    ORDER BY t.oid;

This will show you all fk's in your database.

Details on this can be found at
http://techdocs.postgresql.org/techdocs/hackingreferentialintegrity.php

HTH.

Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton
Knowledge Management & Technology Consultant