Thread: 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.
Attachment
On Dec 11, 2007 10:57 AM, Isaac Vetter <ivetter@math.purdue.edu> wrote:
This should be:
public.table2(id)
Does that fix the problem?
Sean
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;
This should be:
public.table2(id)
Does that fix the problem?
Sean
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