Thread: Adding foreign keys
The add constraint foreign key dialog does not appear
to be working correctly. It doesn't offer any column
choices in the 'referencing' drop down box in the
columns tab. I have a table selected that has the
primary key column I am trying to reference. I have
tried this on multiple tables, both when creating a
new table and then also when creating a new key on an
existing table. I have had to add the foreign key
just using an SQL statement - which works fine.
Also, if I want to create a foreign key that
references the primary key in the same table
('child_of' type columns), I am not able to do a self
reference to the table untill after it has been fully
created. I was sure if this was a bug or simply
hadn't been implemented yet. I seem to recall it
working in previous versions of PgAdmin, but I could
be mistaken and I don't have them around to test.
Here is my configuration
PgAdminIII 1.2.0(Nov 29, 2004)
PostgreSQL 8.0rc1(installed using pginstaller)
Windows XP Service Pack 2
Trent Oliphant
Thanks for a great tool.
Trent Oliphant wrote:
> The add constraint foreign key dialog does not appear
> to be working correctly. It doesn't offer any column
> choices in the 'referencing' drop down box in the
> columns tab. I have a table selected that has the
> primary key column I am trying to reference.
I can't reproduce this. This sounds like what happens when the tables
don't have pks.
> Also, if I want to create a foreign key that
> references the primary key in the same table
> ('child_of' type columns), I am not able to do a self
> reference to the table untill after it has been fully
> created.
This is by design, only already existent tables can be selected at
creation time.
Although it's perfectly legal, self references might lead to trouble
under some maintenance circumstances, so I'd recommend to avoid them.
Regards,
Andreas
Andreas Pflug wrote: > Trent Oliphant wrote: > >> The add constraint foreign key dialog does not appear >> to be working correctly. It doesn't offer any column >> choices in the 'referencing' drop down box in the >> columns tab. I have a table selected that has the >> primary key column I am trying to reference. > > > I can't reproduce this. This sounds like what happens when the tables > don't have pks. > This is strange, I still can't get the columns to show up in the schema that I am working in. However, if I copy the table definitions to a query window in another database I am able to create the tables and if I add a foreign key the columns show up as I would expect them to. (I am not using a public schema in either database but the same schema name as the login name). Any ideas as to what I may have set wrong in my current schema that would cause this behavior? Something with permissions or anything else. I can't get ANY tables to show their columns in the drop down list for any other table in the schema. I have ended up just writing the SQL to create the foreign key constraint - which isn't that big of a deal - this behavior just seemed really strange. Thanks for your help. Trent Oliphant
Trent Oliphant wrote: > The add constraint foreign key dialog does not appear > to be working correctly. It doesn't offer any column > choices in the 'referencing' drop down box in the > columns tab. I have a table selected that has the > primary key column I am trying to reference. I can reproduce that bug if I connect as a normal user, but when I connect as postgres the drop down box works as it should and I can create the foreign key. -- Kim Hansen Vadgårdsvej 3, 2.tv 2860 Søborg Tlf: 39 56 24 37