Thread: dev 1.15 wxwidgets - constraints with multiple tables

dev 1.15 wxwidgets - constraints with multiple tables

From
Colin Beckingham
Date:
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.
-- 
---
Colin Beckingham
613-454-5369
http://www.it4gh.com


Re: dev 1.15 wxwidgets - constraints with multiple tables

From
Guillaume Lelarge
Date:
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.


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



Re: dev 1.15 wxwidgets - constraints with multiple tables

From
Colin Beckingham
Date:

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
PRIMARYKEY (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
PRIMARYKEY (wordid )
 
);
CREATE INDEX words_bookid_idx  ON words  USING btree  (bookid );

-- 
---
Colin Beckingham
613-454-5369
http://www.it4gh.com


Re: dev 1.15 wxwidgets - constraints with multiple tables

From
Guillaume Lelarge
Date:
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?


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



Re: dev 1.15 wxwidgets - constraints with multiple tables

From
Colin Beckingham
Date:

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


Re: dev 1.15 wxwidgets - constraints with multiple tables

From
Guillaume Lelarge
Date:
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.

>  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?

No. Moreover, it wouldn't be consistent with the other dialogs. Having
the table name on the same tab should be enough.

>  Would use of the status bar help guide users?

No. The status bar is only available to remind you if you miss to fill
some required informations.


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



Re: dev 1.15 wxwidgets - constraints with multiple tables

From
Guillaume Lelarge
Date:
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