Relation n does not exist when trying to ad a foregin key

Started by Fabio Furia Silvaabout 23 years ago2 messagesbugs
Jump to latest
#1Fabio Furia Silva
ff-@uol.com.br

Dear all:

I am having this error when I try to add a foreign key:
ERROR: Relation n does not exist

Here is the command:

test=# alter table cep_bairro add FOREIGN KEY (cod_localidade) REFERENCES
cidade (cod_localidade);

NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ERROR: Relation 107890740 does not exist

On the other hand, the same command works for another table, when I try to
link to the same table:

test=# alter table cep_logra add FOREIGN KEY (cod_localidade) REFERENCES
cidade (cod_localidade);

NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY
check(s)
ALTER TABLE
Time: 234175.99 ms

What is is going?

These 3 tables (cep_bairro, cep_logra and cidade) where changed a lot,
either by being recriated or via alter table.

Table cidade has its primary key changed, but both related tables
(cep_bairro, cep_logra) have their corresponded field also changed.

Thanks.

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Fabio Furia Silva (#1)
Re: Relation n does not exist when trying to ad a foregin key

"Fabio Furia Silva" <ff-@uol.com.br> writes:

I am having this error when I try to add a foreign key:
ERROR: Relation n does not exist

What postgres version is this?

These 3 tables (cep_bairro, cep_logra and cidade) where changed a lot,
either by being recriated or via alter table.

If it's pre-7.3, I'd imagine that you got burnt by dropping a table that
was still somehow referenced elsewhere (by a trigger or view, say).
In 7.3 that type of mistake shouldn't be possible anymore, however.

You could find out more by looking through the system catalogs to see
where the dangling reference to OID 107890740 is. Consult the docs for
your version to see which columns in which system tables are references
to pg_class.oid.

regards, tom lane