Re: LOST REFERENTIAL INTEGRITY - Mailing list pgsql-general

"Jimmie H. Apsey" <japsey@futuredental.com> writes:
> Referential Integrity on one of our production tables seems to have been
> lost.  I am running Postgres 7.1.3 embedded within Red Hat
> kernel-2.4.9-e.49.

7.1 is mighty ancient, but ...

> I do not know how to disable referential integrity on a column in a table.
> I do not know how to view what Postgres thinks my referential integrity
> constraints are on this table.

In that version, you'd be talking about triggers on the tables, and it
seems that psql's \d didn't learn to display triggers till later.
You'll need to look at pg_trigger directly.  For example,

regression=# select version();
                             version
------------------------------------------------------------------
 PostgreSQL 7.1.3 on hppa2.0-hp-hpux10.20, compiled by GCC 2.95.3
(1 row)

regression=# create table foo (f1 int primary key);
NOTICE:  CREATE TABLE/PRIMARY KEY will create implicit index 'foo_pkey' for table 'foo'
CREATE
regression=# create table bar (f2 int references foo);
NOTICE:  CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s)
CREATE
regression=# \d foo
          Table "foo"
 Attribute |  Type   | Modifier
-----------+---------+----------
 f1        | integer | not null
Index: foo_pkey

-- drat, no trigger display
regression=# select * from pg_trigger order by oid desc limit 3;
 tgrelid |            tgname            | tgfoid | tgtype | tgenabled | tgisconstraint | tgconstrname | tgconstrrelid |
tgdeferrable| tginitdeferred | tgnargs | tgattr |                         tgargs 

---------+------------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+--------------------------------------------------------
 2913646 | RI_ConstraintTrigger_2913673 |   1655 |     17 | t         | t              | <unnamed>    |       2913659 |
f           | f              |       6 |        | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\000 
 2913646 | RI_ConstraintTrigger_2913671 |   1654 |      9 | t         | t              | <unnamed>    |       2913659 |
f           | f              |       6 |        | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\000 
 2913659 | RI_ConstraintTrigger_2913669 |   1644 |     21 | t         | t              | <unnamed>    |       2913646 |
f           | f              |       6 |        | <unnamed>\000bar\000foo\000UNSPECIFIED\000f2\000f1\000 
(3 rows)

regression=#

Each FK constraint should have three associated triggers (two on the
referencing table, one on the referenced table).  You can sort out which
is which by looking at the tgargs field --- note how the referencing and
referenced table and field names are embedded in that.  I suspect that
some of these triggers got dropped or disabled.

If you don't find all three triggers for some one constraint, the best
bet is to drop any remaining triggers from the set and then issue ALTER
TABLE ADD FOREIGN KEY to re-make a consistent trigger set.

            regards, tom lane