Обсуждение: 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