Postgres Pro
Downloads
Home   >   mailing lists

Thread: Cannot Create Foreign Key

Cannot Create Foreign Key

From
"Shane G. Brodie"
Date:
App:    pgAdminIII V1.2.0 Post Beta 1 (Sept 17 2004)
DB:    PostgreSQL 7.4.2
O/S:    Fedora Core 2
Kernel: 2.6.8-1.521

Cannot add a foreign key to a table using the gui provided by the "New
Object" ... "Foreign Key" dialog.  The dialog appears complete and I
have filled out all of the requisite parts of the dialog.  On the
"Columns" tab, the two related columns are selected, but the "Add"
button remains grayed out.

Regards

Shane Brodie
Winnipeg, Manitoba, Canada


PS:  SQL for the 2 tables in case I've lost my marbles and am trying to
do something I shouldn't be, is included below.


CREATE TABLE emp_tb
( emp_id serial NOT NULL, familyname varchar(30) NOT NULL, givennames varchar(30) NOT NULL, sinnumber varchar(9), dob
date,photo_path varchar(255), CONSTRAINT pk_emp_tb PRIMARY KEY (emp_id)
 
) 
WITHOUT OIDS;

CREATE TABLE emp_address_tb
( emp_id int4 NOT NULL, address_id serial NOT NULL, add1 varchar(100) NOT NULL, add2 varchar(100), city varchar(50) NOT
NULL,prov_cd char(2) NOT NULL, postalcd char(6), CONSTRAINT pk_emp_address PRIMARY KEY (address_id)
 
) 
WITHOUT OIDS;

Relation I'm trying to establish is between emp_tb.emp_id and
emp_address_tb.emp_id ...

Re: Cannot Create Foreign Key

From
Andreas Pflug
Date:
Shane G. Brodie wrote:
> App:    pgAdminIII V1.2.0 Post Beta 1 (Sept 17 2004)
> DB:    PostgreSQL 7.4.2
> O/S:    Fedora Core 2
> Kernel: 2.6.8-1.521
> 
> Cannot add a foreign key to a table using the gui provided by the "New
> Object" ... "Foreign Key" dialog.  The dialog appears complete and I
> have filled out all of the requisite parts of the dialog.  On the
> "Columns" tab, the two related columns are selected, but the "Add"
> button remains grayed out.

If you change a column, Add is enabled.
I committed a change to CVS which hopefully fixes this issue.

Regards,
Andreas

Re: Cannot Create Foreign Key

From
Richard_D_Levine@raytheon.com
Date:
I haven't worked with PostgreSQL much at all yet, but doesn't there need to
be a primary key constraint on the emp_address_tb's emp_id column in order
to set up the foreign key relation between the two emp_id columns?  --Rick


                                          Andreas Pflug
                                                               <pgadmin@pse-consulting.d        To:
sgbrodie@mts.net                                                                                  e>
          cc:       pgadmin-support@postgresql.org
Sentby:                         Subject:  Re: [pgadmin-support] Cannot Create Foreign Key
                    pgadmin-support-owner@pos
                                         tgresql.org


                                                                                                        09/18/2004
04:01AM


                              
 




Shane G. Brodie wrote:
> App:             pgAdminIII V1.2.0 Post Beta 1 (Sept 17 2004)
> DB:        PostgreSQL 7.4.2
> O/S:             Fedora Core 2
> Kernel: 2.6.8-1.521
>
> Cannot add a foreign key to a table using the gui provided by the "New
> Object" ... "Foreign Key" dialog.  The dialog appears complete and I
> have filled out all of the requisite parts of the dialog.  On the
> "Columns" tab, the two related columns are selected, but the "Add"
> button remains grayed out.

If you change a column, Add is enabled.
I committed a change to CVS which hopefully fixes this issue.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Cannot Create Foreign Key

From
Andreas Pflug
Date:
Richard_D_Levine@raytheon.com wrote:
> I haven't worked with PostgreSQL much at all yet, but doesn't there need to
> be a primary key constraint on the emp_address_tb's emp_id column in order
> to set up the foreign key relation between the two emp_id columns? 

Sort of, there has to be a unique index (which a pk by design is).
in a future version (1.3/1.4) we might have some autodetect mechanism 
for those columns.

Regards,
Andreas

Re: Cannot Create Foreign Key

From
Richard_D_Levine@raytheon.com
Date:
That makes sense.  Thanks Andreas --Rick


                                          Andreas Pflug
                                                               <pgadmin@pse-consulting.d        To:
Richard_D_Levine@raytheon.com                                                                     e>
          cc:       pgadmin-support@postgresql.org, pgadmin-support-owner@postgresql.org,
Sentby:                          sgbrodie@mts.net
                    pgadmin-support-owner@pos        Subject:  Re: [pgadmin-support] Cannot Create Foreign Key
                                         tgresql.org


                                                                                                        09/20/2004
10:15AM


                              
 




Richard_D_Levine@raytheon.com wrote:
> I haven't worked with PostgreSQL much at all yet, but doesn't there need
to
> be a primary key constraint on the emp_address_tb's emp_id column in
order
> to set up the foreign key relation between the two emp_id columns?

Sort of, there has to be a unique index (which a pk by design is).
in a future version (1.3/1.4) we might have some autodetect mechanism
for those columns.

Regards,
Andreas

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html