Thread: dev 1.15 wxwidgets - constraints with multiple tables
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
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
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
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
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
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
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