Date: Sun, 21 Jul 2019 13:53:58 +0000 (UTC)
From: Karen Goh <karenworld@yahoo.com>
To: pgsql-admin@lists.postgresql.org, Holger Jakobs <holger@jakobs.com>
Message-ID: <385857992.2643116.1563717238725@mail.yahoo.com>
In-Reply-To: <5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com>
References: <820008379.2579287.1563670685484@mail.yahoo.com>
 <5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com>
Subject: Re: How do I alter an existing column and add a foreign key which
 is a Primary key to a table?
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_2643115_983167770.1563717238724"
Content-Length: 4178
Precedence: bulk

------=_Part_2643115_983167770.1563717238724
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: 7bit

 
On Sunday, July 21, 2019, 3:28:10 PM GMT+8, Holger Jakobs <holger@jakobs.com> wrote:


Am 21. Juli 2019 02:58:05 MESZ schrieb Karen Goh <karenworld@yahoo.com>:
>Hi all,
>
>With PG4Admin, I am lost.

Since pgadmin4 is just a frontend, you're not "lost with pgadmin4", but maybe with postgresql.

>I realised now that the keys are not created and perhaps that is why
>the join query is not working out.

Creating a foreign key doesn't magically create the necessary keys for it.


>Please let me know what is the correct way to alter a column in a table
>to have foreign key to a tutor_id which is also the primary key of that
>table.
>
>So, meaning I need to create a foreign key as well as primary key for
>tutor_id.
>
>So far, this is what I have attempted but it is not working.
>ALTER TABLE tutor_subject
> ADD CONSTRAINT tutor_subject_pk
> PRIMARY KEY (tutor_id)
> ADD CONSTRAINT tutor_subject_fk
> FOREIGN KEY (tutor_id)


This looks like a 1:1 relationship which is to be avoided.

One ALTER TABLE command either adds a primary or a foreign key. There is no FK without the keyword REFERENCES.

FK relationships help in keeping the data consistent, but they are totally unrelated to/useless for SELECT statements and their JOIN operations. This is a common misconception.

Hi Holger, 

After reading your reply, I am confused now.

Cos without creating foreign key in my tutor_subject how am I going to retrieve the zipcode at s_tutor table that meet the list of subject_name and tutor_id in tutor_subject ? There should have some reference right ? If not, how does the database tell this zipcode belong to which tutor_id ?


Regards,

Holger

-- 
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
  
------=_Part_2643115_983167770.1563717238724
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>                <br><br><br><br><br>On Sunday, July 21, 2019, 3:28:10 =
PM GMT+8, Holger Jakobs &lt;holger@jakobs.com&gt; wrote:<br><br><br><br><br=
>Am 21. Juli 2019 02:58:05 MESZ schrieb Karen Goh &lt;karenworld@yahoo.com&=
gt;:<br>&gt;Hi all,<br>&gt;<br>&gt;With PG4Admin, I am lost.<br><br>Since p=
gadmin4 is just a frontend, you&#39;re not &quot;lost with pgadmin4&quot;, =
but maybe with postgresql.<br><br>&gt;I realised now that the keys are not =
created and perhaps that is why<br>&gt;the join query is not working out.<b=
r><br>Creating a foreign key doesn&#39;t magically create the necessary key=
s for it.<br><br><br>&gt;Please let me know what is the correct way to alte=
r a column in a table<br>&gt;to have foreign key to a tutor_id which is als=
o the primary key of that<br>&gt;table.<br>&gt;<br>&gt;So, meaning I need t=
o create a foreign key as well as primary key for<br>&gt;tutor_id.<br>&gt;<=
br>&gt;So far, this is what I have attempted but it is not working.<br>&gt;=
ALTER TABLE tutor_subject<br>&gt;  ADD CONSTRAINT tutor_subject_pk<br>&gt; =
   PRIMARY KEY (tutor_id)<br>&gt;    ADD CONSTRAINT tutor_subject_fk<br>&gt=
;    FOREIGN KEY (tutor_id)<br><br><br>This looks like a 1:1 relationship w=
hich is to be avoided.<br><br>One ALTER TABLE command either adds a primary=
 or a foreign key. There is no FK without the keyword REFERENCES.<br><br>FK=
 relationships help in keeping the data consistent, but they are totally un=
related to/useless for SELECT statements and their JOIN operations. This is=
 a common misconception.<br><br>Hi Holger, <br><br>After reading your reply=
, I am confused now.<br><br>Cos without creating foreign key in my tutor_su=
bject how am I going to retrieve the zipcode at s_tutor table that meet the=
 list of subject_name and tutor_id in tutor_subject ?  There should have so=
me reference right ?  If not, how does the database tell this zipcode belon=
g to which tutor_id ?<br><br><br><br>Regards,<br><br>Holger<br><br>-- <br>H=
olger Jakobs, Bergisch Gladbach<br>+49 178 9759012<br>- sent from mobile, t=
herefore short -<br>            </div>           =20
------=_Part_2643115_983167770.1563717238724--