Thread: Dropped table screws referential integrity?
I had a table (job_documents) that referenced another (jobs). I no longer needed job_documents, so I dropped it. Now, any attempt to delete rows from jobs fails--it complains that job_documents doesn't exist. Is there a way to fix this? PostgreSQL 7.1.3/FreeBSD 4.2R TIA ------------------------------------------------------ Joel Mc Graw DataBill, LLC 602-415-1234 ext. 13 -----BEGIN GEEK CODE BLOCK----- Version: 3.1 GCS d-@ s: a C++++ UB++++ P-- L- E? W++ N w--- O? M+ V PS+++ PE++ Y+ PGP++ t+ 5++ X tv+ b+ DI++ G e++ h---- r+++ y++++ -----END GEEK CODE BLOCK-----
On Thu, 7 Mar 2002, Joel Mc Graw wrote: > I had a table (job_documents) that referenced another (jobs). I no > longer needed job_documents, so I dropped it. Now, any attempt to > delete rows from jobs fails--it complains that job_documents doesn't > exist. Is there a way to fix this? You'll need to find the two triggers on jobs that reference job_documents (do a select * from pg_trigger and look at the argument list to find them) and drop them and you should be fine.
Joel Mc Graw <jmcgraw@databill.com> writes:
> I had a table (job_documents) that referenced another (jobs). I no
> longer needed job_documents, so I dropped it. Now, any attempt to
> delete rows from jobs fails--it complains that job_documents doesn't
> exist. Is there a way to fix this?
Drop the triggers on jobs that enforce the RI check. They should've
been dropped automatically, but I believe there used to be a bug in
pg_dump that caused the necessary linkage data not to be included in
dumps --- so if your DB has been dumped and reloaded since the triggers
were made, I'm not surprised you'd have got into this state.
See mail list archives of past discussions if you need help finding
the triggers (hint: look in pg_trigger, and beware that the triggers
you want to drop have mixed-case names which you will have to double
quote). There will be either one or two triggers to get rid of
depending on which end of the RI check this table was.
regards, tom lane
On Thu, 7 Mar 2002, Stephan Szabo wrote: > > On Thu, 7 Mar 2002, Joel Mc Graw wrote: > > > I had a table (job_documents) that referenced another (jobs). I no > > longer needed job_documents, so I dropped it. Now, any attempt to > > delete rows from jobs fails--it complains that job_documents doesn't > > exist. Is there a way to fix this? > > You'll need to find the two triggers on jobs that reference job_documents > (do a select * from pg_trigger and look at the argument list to find > them) and drop them and you should be fine. In techdocs.postgresql.org, I have a HOWTO on referential integrity that provides some views that make it easier to find the ref rules in effect in your tables. These are helpful for debugging. -- Joel BURTON | joel@joelburton.com | joelburton.com | aim: wjoelburton Independent Knowledge Management Consultant