Обсуждение: schema question

Hi All;

I'm much more of a web developer than dba, that's why I'm asking this of
the novice list.

I've got a database in postgresql 7.4 with 111 tables.

I recently created the 111th table and I cannot create foreign key
constraints.

Postgresql acts like the new table is in a different schema that the
referenced table. Additionally, if I go back to a previously created
table and try to rename a foreign key column (by dropping and adding) I
cannot recreate the foreign key. The error that I get is that the
referenced schema does not exist:

ERROR:  schema "table2" does not exist

If I specify the schema name on both tablenames, when adding the constraint:

alter table public.table1 add foreign key(table2_id) references
public.table2.id;

I get this error:
ERROR:  cross-database references are not implemented


I know very little about postgresql's schema's. Am I doing things
correctly? I've never had this kind of a problem before. Postgresql has
treated me very well. Has anyone else experienced similar problems?

Isaac Vetter

p.s. I know that I need to upgrade to v8. That's not possible right now.

Sean Davis wrote:
>     I recently created the 111th table and I cannot create foreign key
>     constraints.
>
>     Postgresql acts like the new table is in a different schema that the
>     referenced table. Additionally, if I go back to a previously created
>     table and try to rename a foreign key column (by dropping and adding) I
>     cannot recreate the foreign key. The error that I get is that the
>     referenced schema does not exist:
>
>     ERROR:  schema "table2" does not exist
>
>     If I specify the schema name on both tablenames, when adding the
>     constraint:
>
>     alter table public.table1 add foreign key(table2_id) references
>     public.table2.id ;
>
>
> This should be:
>
> public.table2(id)
>
> Does that fix the problem?


OMG!

This was exactly the problem: a stupid syntax error.

I've literally created hundreds of foreign keys in pgsql, but did not
see the problem with this one.

Much thanks, Sean.

Isaac