Date: Sun, 21 Jul 2019 01:31:39 +0000 (UTC)
From: Karen Goh <karenworld@yahoo.com>
To: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
Message-ID: <1813907165.2572427.1563672699363@mail.yahoo.com>
In-Reply-To: <24840698-956d-31fe-96dd-9e9427890e39@gmail.com>
References: <820008379.2579287.1563670685484@mail.yahoo.com>
 <24840698-956d-31fe-96dd-9e9427890e39@gmail.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_2572426_1731224003.1563672699362"
Content-Length: 4738
Precedence: bulk

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

 
On Sunday, July 21, 2019, 9:25:54 AM GMT+8, Ron <ronljohnsonjr@gmail.com> wrote:


On 7/20/19 7:58 PM, Karen Goh wrote:

> Hi all,
>
> I used to write a script in MYSQL and foreign and primary key will be created.
>
> With PG4Admin, I am lost.
>
> I realised now that the keys are not created and perhaps that is why the join query is not working out.
>
> 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)


What error message do you get?

Does tutor_id already exist in tutor_subject?

Yes. It is already there but it is the first time I used pgAdmin4 so I just used the add column to put in the infor.

Now, I just tried want to do one thing first which is to alter the tutor_id in tutor_subject to a primary key.

ALTER TABLE tutor_subject
 ADD CONSTRAINT tutor_subject_pk 
 PRIMARY KEY (tutor_id)

But, am receiving error messagte :

ERROR: could not create unique index "tutor_subject_pk"
DETAIL: Key (tutor_id)=(0) is duplicated.
SQL state: 23505

I noticed several of the rows has 0 at tutor_id. It must have attributed to the table not created properly.

How do I resolve this ? delete those rows?

What foreign table are you referencing? (I don't see that referenced in
your example.)

The foreign table will be s_tutor which has a tutor_id as well.

So, the tutor_id in tutor_subject will be both primary key as well as foreign key.

Have you read the documentation?
https://www.postgresql.org/docs/9.6/sql-altertable.html
http://www.postgresqltutorial.com/postgresql-primary-key/
http://www.postgresqltutorial.com/postgresql-foreign-key/


-- 
Angular momentum makes the world go 'round.


------=_Part_2572426_1731224003.1563672699362
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div>                <br><br><br><br><br>On Sunday, July 21, 2019, 9:25:54 =
AM GMT+8, Ron &lt;ronljohnsonjr@gmail.com&gt; wrote:<br><br><br>On 7/20/19 =
7:58 PM, Karen Goh wrote:<br><br>&gt; Hi all,<br>&gt;<br>&gt; I used to wri=
te a script in MYSQL and foreign and primary key will be created.<br>&gt;<b=
r>&gt; With PG4Admin, I am lost.<br>&gt;<br>&gt; I realised now that the ke=
ys are not created and perhaps that is why the join query is not working ou=
t.<br>&gt;<br>&gt; Please let me know what is the correct way to alter a co=
lumn in a table to have foreign key to a tutor_id which is also the primary=
 key of that table.<br>&gt;<br>&gt; So, meaning I need to create a foreign =
key as well as primary key for tutor_id.<br>&gt;<br>&gt; So far, this is wh=
at I have attempted but it is not working.<br>&gt; ALTER TABLE tutor_subjec=
t<br>&gt;    ADD CONSTRAINT tutor_subject_pk<br>&gt;      PRIMARY KEY (tuto=
r_id)<br>&gt;     ADD CONSTRAINT tutor_subject_fk<br>&gt;     FOREIGN KEY (=
tutor_id)<br><br><br>What error message do you get?<br><br>Does tutor_id al=
ready exist in tutor_subject?<br><br>Yes.  It is already there but it is th=
e first time I used pgAdmin4 so I just used the add column to put in the in=
for.<br><br>Now, I just tried want to do one thing first which is to alter =
the tutor_id in tutor_subject to a primary key.<br><br>ALTER TABLE tutor_su=
bject<br>  ADD CONSTRAINT tutor_subject_pk <br>    PRIMARY KEY (tutor_id)<b=
r><br>But, am receiving error messagte :<br><br>ERROR:  could not create un=
ique index &quot;tutor_subject_pk&quot;<br>DETAIL:  Key (tutor_id)=3D(0) is=
 duplicated.<br>SQL state: 23505<br><br>I noticed several of the rows has 0=
 at tutor_id.  It must have attributed to the table not created properly.<b=
r><br>How do I resolve this ? delete those rows?<br><br>What foreign table =
are you referencing?  (I don&#39;t see that referenced in<br>your example.)=
<br><br>The foreign table will be s_tutor which has a tutor_id as well.<br>=
<br>So, the tutor_id in tutor_subject will be both primary key as well as f=
oreign key.<br><br>Have you read the documentation?<br>https://www.postgres=
ql.org/docs/9.6/sql-altertable.html<br>http://www.postgresqltutorial.com/po=
stgresql-primary-key/<br>http://www.postgresqltutorial.com/postgresql-forei=
gn-key/<br><br><br>-- <br>Angular momentum makes the world go &#39;round.<b=
r><br><br><br>            </div>           =20
------=_Part_2572426_1731224003.1563672699362--