Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?

On Sunday, July 21, 2019, 3:28:10 PM GMT+8, Holger Jakobs <holger(at)jakobs(dot)com> wrote:

Am 21. Juli 2019 02:58:05 MESZ schrieb Karen Goh <karenworld(at)yahoo(dot)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 -