Re: dev 1.15 wxwidgets - constraints with multiple tables - Mailing list pgadmin-support

On Fri, 2012-04-06 at 22:55 +0200, Guillaume Lelarge wrote:
> On Fri, 2012-04-06 at 16:39 -0400, Colin Beckingham wrote:
> > 
> > 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?
> 
> Could be. I wouldn't object to put the table combobox in the third tab.
> 
> >  Does selecting the referencing table belong on 
> > the same tab as selecting the referencing field?
> 
> Sure, could be nice.
> 

Done. Will be available in 1.16.


-- 
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com