Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hp198-0000I7-DS for pgsql-admin@arkaria.postgresql.org; Sun, 21 Jul 2019 02:00:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hp195-0000NS-Qd for pgsql-admin@arkaria.postgresql.org; Sun, 21 Jul 2019 02:00:15 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hp195-0000NL-Cw for pgsql-admin@lists.postgresql.org; Sun, 21 Jul 2019 02:00:15 +0000 Received: from sonic316-12.consmr.mail.bf2.yahoo.com ([74.6.130.122]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.92) (envelope-from ) id 1hp192-000649-2h for pgsql-admin@lists.postgresql.org; Sun, 21 Jul 2019 02:00:14 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1563674410; bh=SXyQpe9/gxTYoEW2DFM5GRBA1GdvvqIzAyC+XSmPLWQ=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject; b=n9YRowuARDuuQyTh04dqP3z1g++LpvRFLxj+uF4n/T81bNm1uTG7ZLyrya/jqjK88BJJTft1+Tb9izEowx9+c2W7dYiHbQIx5d+KF4NQx+49jEJLpqYUzVsrUNINMc278qikQH72i0uM1mDEdL56M46xFiwzfEbZT4mFXyG9LpUHIiNEe0rtgK7CboKNNi+zlMH3uEp86BP5hl7wrhBVO8BmM5DTMEcBP7TLMCD9uw43/+kURUEQnIV6zCM0mIO8ja5uFNMlYEfTGoFPpymfo0OCdC4SvNnPUZmNtk+/xy6ndyk7WEZ2ZryszctUX/9MKJLNQewVCG1OGOWMhZNK0g== X-YMail-OSG: t0xAsIkVM1mFYLrZoauRn4KaDdiXJrNm8PXTWUpa__gbycCYGYckM_G_Uf0TrxT 4d6kUosC7o5WGo6IMm4w3FGhisV3K65GKkvkB9NnUqy.ms71m1Iqdk.Mc9bSXE4rN8grqEXwgT_D At9Y95frrExa4U.1ezt9cOuLrbHr2H6ykcPoarpSz7p2qM8Kwj4niSQm_NPO7HqvNqHzfzTXuD_B CyzggvFd9Q7baLybV.3zOvzIKhrM45yyA4vjkTioCcoZB95vX3YWTXkTtmyKo1O1ePgGNUofQG1c k_HKJrr2uU9G7QgwdXodsW277lB5o3VBZn6C0hOcytfF_nfd43Xnkskm3SqmUViCtUtOv5h15p8I WlvjsKWBfIvXfIrw19HScpaapOdKxuZSyB1g0yNJRZzug._AFmCEIuh1betIFpZJhD_FsBQ0lnPE lHnIEpXT4q4Al6xj_FiMV37_G8ynfSP95ysI9lqwWt7gzfieEe6RJnoX_5aB1IckL6HDqSKbm7_1 N.fo7FYqDb_KpPwNPdAF.copgn_8jbF5ZLIknjlCykT2wAaZP2aODQjUZLFhVpck5pNugmvJuxi_ GaQ1QblfXdMOYFDEq1pSrzLxKyUWZJbvWKLLwgly.4uvY0AGziqMTSPZtA.8lar8VyqEqSdzE8CL 6SUdpKHVNUSy7oI3dO8Wyim89z2r85pGegRfjyyGOGYFD6Ff1UbNr_h30OkhHroAPHJAlYEI.y24 Le8aOOOnlhxXdDaYQy9yUGRV5LvjiaeVkYyOrZPlT7GTPw_PxhO5PjKqZaT6UytJOkm9ZQ1H7GGu Bi0Qx_uqQzsFAdqtq0Q7.4fP9YhYca9K2Wb7mJSpYuLjcneEqGyEmFOIsh2bKPXFfMEZKtCmhdgR H7a.ykrYEZ719tG.q3t_bptI.J.6GsBg7NoIwTSfCTH7cfarUx6VGqIp3yFFhAvYGMaHIpJEsAGd I8AfRP.709v3IwntBm03tjipGpiQvBb0JXHIOFAyY00o4PInTM7rohHhNkeVUVb9U9owfzAok7Lp 7XFskny96O6H5W7_vIL73iDH.aOX46P6A.IGXSvvcO.EIo6OPVtG3at2HIILX9moZw.OC50wVzn3 wIIJdAKXAayYCsr6hX09EdxTEy2nkh7.1FS7R8eKChQ8J_VNwFT6_oRgw1Igj78c9zqQRtBzV0Xl rDdtH.o3daFsob6OZFPqPa_KW.JtJQLJnNnuOMmC2sia_FnoSFqLi97gT6qbBL8g8BTPHC69LxPx xSO2fd6Q5omNL8W19ae4S.m3ZZ6_07yXq Received: from sonic.gate.mail.ne1.yahoo.com by sonic316.consmr.mail.bf2.yahoo.com with HTTP; Sun, 21 Jul 2019 02:00:10 +0000 Date: Sun, 21 Jul 2019 02:00:08 +0000 (UTC) From: Karen Goh To: pgsql-admin@lists.postgresql.org, Ron Message-ID: <337537607.2613319.1563674408215@mail.yahoo.com> In-Reply-To: <8da1d777-b5d3-30e1-e942-424ece88c4de@gmail.com> References: <820008379.2579287.1563670685484@mail.yahoo.com> <24840698-956d-31fe-96dd-9e9427890e39@gmail.com> <1813907165.2572427.1563672699363@mail.yahoo.com> <8da1d777-b5d3-30e1-e942-424ece88c4de@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_2613318_1505558152.1563674408213" Content-Length: 6765 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2613318_1505558152.1563674408213 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit On Sunday, July 21, 2019, 9:49:13 AM GMT+8, Ron wrote: On 7/20/19 8:31 PM, Karen Goh wrote: > > On Sunday, July 21, 2019, 9:25:54 AM GMT+8, Ron > 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? Naturally. You can't have a unique index with duplicate keys. Sorry Ron. I just realised that my tutor_id needs to contain duplication becuase of my use case. Basically, tutor_subject is a 'JOIN' table so it will have duplicate tutor_id as it is a many-to-many relationship design. In this case, what should I do then since I can't make tutor_id a Primary key but yet it has to reference s_tutor.tutor_id as foreign key? > > 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. You can't just say "tutor_id is a foreign key"; you've got to tell it the name of the foreign table. > > 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. > > > -- Angular momentum makes the world go 'round. ------=_Part_2613318_1505558152.1563674408213 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable





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


On 7/20/19 = 8:31 PM, Karen Goh wrote:
>
> On Sunday, July 21, 2019, 9:25:54= AM GMT+8, Ron <ronljohnsonjr@gmail.com>
> wrote:
>
&g= t;
> On 7/20/19 7:58 PM, Karen Goh wrote:
>
> > Hi all= ,
> >
> > I used to write a script in MYSQL and foreign a= nd primary key will be
> created.
> >
> > With PG4A= dmin, 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 t= o alter a column in a table
> to have foreign key to a tutor_id which= is also the primary key of that table.
> >
> > So, meani= ng 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 CON= STRAINT tutor_subject_pk
> > PRIMARY KEY (tutor_id)
> > A= DD CONSTRAINT tutor_subject_fk
> > FOREIGN KEY (tutor_id)
><= br>>
> What error message do you get?
>
> Does tutor_i= d 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 col= umn to put in the infor.
>
> Now, I just tried want to do one t= hing first which is to alter the
> tutor_id in tutor_subject to a pri= mary key.
>
> ALTER TABLE tutor_subject
> ADD CONSTRAINT = tutor_subject_pk
> PRIMARY KEY (tutor_id)
>
> But, am rec= eiving error messagte :
>
> ERROR: could not create unique inde= x "tutor_subject_pk"
> DETAIL: Key (tutor_id)=3D(0) is dupl= icated.
> SQL state: 23505
>
> I noticed several of the r= ows has 0 at tutor_id. It must have attributed
> to the table not cre= ated properly.
>
> How do I resolve this ? delete those rows?
Naturally. You can't have a unique index with duplicate keys.
Sorry Ron. I just realised that my tutor_id needs to contain duplicat= ion becuase of my use case.
Basically, tutor_subject is a 'JOIN'= table so it will have duplicate tutor_id as it is a many-to-many relations= hip design.

In this case, what should I do then since I can't ma= ke tutor_id a Primary key but yet it has to reference s_tutor.tutor_id as f= oreign key?


>
> 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.

You can't just say "tutor_id is a for= eign key"; you've got to tell it the
name of the foreign table.=


>
> Have you read the documentation?
> https://w= ww.postgresql.org/docs/9.6/sql-altertable.html
> http://www.postgresq= ltutorial.com/postgresql-primary-key/
> http://www.postgresqltutorial= .com/postgresql-foreign-key/
>
>
> --
> Angular mom= entum makes the world go 'round.
>
>
>

--
= Angular momentum makes the world go 'round.


=20 ------=_Part_2613318_1505558152.1563674408213--