agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Holger Jakobs <holger@jakobs.com>
To: pgsql-admin@lists.postgresql.org,Karen Goh <karenworld@yahoo.com>
Subject: Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
Date: Sun, 21 Jul 2019 09:28:04 +0200
Message-ID: <5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com> (raw)
In-Reply-To: <820008379.2579287.1563670685484@mail.yahoo.com>
References: <820008379.2579287.1563670685484@mail.yahoo.com>
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.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
view thread (26+ messages) latest in thread
Message-ID: <5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com>
Permalink: ../5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com/
Also on: postgresql.org/message-id/5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: holger@jakobs.com, karenworld@yahoo.com
Subject: Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
In-Reply-To: <5CE41E80-274A-44DA-9553-E2DCA260F751@jakobs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox