Re: dev 1.15 wxwidgets - constraints with multiple tables

On 04/06/2012 04:10 PM, Guillaume Lelarge wrote:
> On Fri, 2012-04-06 at 15:11 -0400, Colin Beckingham wrote:
>>
>> On 04/06/2012 02:40 PM, Guillaume Lelarge wrote:
>>> On Fri, 2012-04-06 at 08:03 -0400, Colin Beckingham wrote:
>>>> Given 3 tables, authors, books and words, each has a primary key.
>>>>
>>>> Attempting to use pgadmin to add a foreign key to books to link authors
>>>> to books, and a key to words to link words to books.
>>>>
>>>> The first step works ok (ignoring previously reported backtrace) and the
>>>> foreign key is added to books linking it to authors. However when
>>>> attempting to add a foreign key to words, pgadmin can still only see the
>>>> authors table as a reference table.
>>>>
>>>> Tried reloading and refreshing but pgadmin does not seem to see beyond
>>>> the authors table for the 'referencing' dropdown.
>>>>
>>>> Maybe I am missing a step. table books definitely has a primary key.
>>>
>>> Can you give us your tables' definition? Thanks.
>>>
>>>
>>
>> CREATE TABLE authors
>> (
>>     authid serial NOT NULL,
>>     name character varying(250) NOT NULL,
>>     CONSTRAINT authors_pkey PRIMARY KEY (authid )
>> );
>> CREATE TABLE books
>> (
>>     bookid serial NOT NULL,
>>     authid integer NOT NULL,
>>     title character varying(250) NOT NULL,
>>     CONSTRAINT books_pkey PRIMARY KEY (bookid ),
>>     CONSTRAINT books_authid_fkey FOREIGN KEY (authid)
>>         REFERENCES authors (authid) MATCH SIMPLE
>>         ON UPDATE NO ACTION ON DELETE NO ACTION,
>>     CONSTRAINT books_bookid_key UNIQUE (bookid )
>> );
>> CREATE TABLE words
>> (
>>     wordid serial NOT NULL,
>>     lemma character varying(100),
>>     bookid integer,
>>     wcount integer,
>>     CONSTRAINT words_pkey PRIMARY KEY (wordid )
>> );
>> CREATE INDEX words_bookid_idx
>>     ON words
>>     USING btree
>>     (bookid );
>>
>
> Thanks. So, I don't see any issues adding an fkey on words. Can you
> describe more precisely what you do?
>
>

Okay, found my error. It works correctly if you follow the right steps.

My mistake was to go straight to the columns tab of the _second_ dialog 
box, skipping the definition tab where I can select a table with the 
reference fields. Missed it entirely and did not think to look there.

So, open pgadmin, drill down to table name. Right click to get context 
menu and select Properties. Select constraints tab, defaults to fkey in 
lower dropdown, click add. New dialog opens.

(At this point the parent of the new dialog is still active so evidently 
the new dialog is not modal. This might lead to problems.)

Continuing - at this point user is tempted to go straight to the columns 
tab but this is a mistake unless the default happens to be what you 
need. Your table might be there but probably not. Next tab should be 
definition where you have a chance to select the referencing table 
before selecting columns, in particular the referencing fields.

Thoughts - is it confusing to the user to have two important fields on 
different dialog tabs? Does selecting the referencing table belong on 
the same tab as selecting the referencing field? Yet again, would it be 
clearer for the user if the referencing field contained a list of 
table.column entries like author.authid and words.lemma? Would use of 
the status bar help guide users?
-- 
---
Colin Beckingham
613-454-5369
http://www.it4gh.com