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 1hpDG3-0002kC-Ml for pgsql-admin@arkaria.postgresql.org; Sun, 21 Jul 2019 14:56:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hpDG2-0006iW-A4 for pgsql-admin@arkaria.postgresql.org; Sun, 21 Jul 2019 14:56:14 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hpDG1-0006iP-W9 for pgsql-admin@lists.postgresql.org; Sun, 21 Jul 2019 14:56:14 +0000 Received: from sonic311-13.consmr.mail.bf2.yahoo.com ([74.6.131.123]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hpDFy-0004DW-KM for pgsql-admin@lists.postgresql.org; Sun, 21 Jul 2019 14:56:13 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1563720967; bh=KNcee7ct445qGjpxDzIRU7vAEdDoHB3PxabOnpC38XY=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject; b=ifcS2VB0UhjZXp888nFZlZVdgbC069cY1LhTwUS9fFzBg0k8HDcG4T+SKOSvluNdn4zbKBBfkvWntzbBYymn2lqltPUR4Ys8+Grd5102aw9BUIYp9W3TUFG4JHr48T0gO1pHwVdbuIHArubwnBLT6/2jzZY2ZFCHViVXCrp9w/MOhWZ7p+tUbhg6STusFDgJoqgZU78BNxQnaY0dN26qPnk8IS3uOgj46IQFrQl20zlvjcYKf49w8tZWNv8m5H2VYU1UHAMmWW0+YU2LYnGFa9FAqXiPFt4hbcBWBO+mclc2z1/hCH9yfmjIArMEOlZ3D3a61MZmpz2CkHLhIX1MYA== X-YMail-OSG: l49hLisVM1nKE30Egu.qFK5CqIV2BnzjTgHPoQ1aeID1eEMtsgj0AwNinZSxSiJ hSqYAgct6I1DTE1P0U3SD7L.9v5S9VR2qVt_B0ZWyhfJNifxBfN0iKgk4COB.fha3pUeTDjNymLu Nfpv7WeqN.SBt3wRYvPCV_8unqYY_LQ5TgxkKnLr761SzhMKPlnaJQdRw3JFIaGItXZA7MKbIMY3 YFQOzRARKvW9RCbrMNJJQ9ltb1nCg7.kW0qLu3qAylRyKXDqA8OKqY3czJXcXljI0RtvxVB6I9Fm wP.mDwlwd_TNSZqp3Kahdy4qTU6fJf7tf4maSVR6Wr9rVQW4cBaJzOGBJ28RuoQX1MIDXQu3S2RK G9DPlN7Vy_h0vKo7GvEu47060AoN_WQB06vh2gsEWDbqJQnznhb60DQ1yDBpBJUPulFgcE6rm.3m rw0gL7v48WtQAV3J9XNAoq9SIEGnVlAKGQybWpn.O377.eNUCi83vpz0nZraFrKoJv26voaHfe15 fCMVC7srxdZ8Wa34bTaXIKPTHzmgSRbIdo9k1z9fSaUC3BZNHYqWRVDKircC9LDz8FtXB02.9uP7 bz8HqFIBhE9US6u8GSYPogJ4pewVpnXsqrBr7Wo9ESx1pOqYw8pa1Nto749ff5wNfRLCoen4fSma 3RLB_yc3lTNJAMzrOLDyj4Xhmq8tCaKAz9RFM8gg1Naky_33WR32lP0hpsVYDY6a4ipFDn.kQ4K0 AhIsieXOa5rn182oDlLE0nc0.amEQLesXxJsOaYzEMoA.vZtyBWz99vSf8wVRC2oDrMFZ07AKXdm KpIzTFYk0mgIiXqvJvuomkD3rnkrtQL77gAX5Fi.jWfgPH67TWXRi1il8VWKNBd8JdyQryz4VI80 Buz5OXlY.LHMJ4iL2MCjLHY0FppDRyr8sVWsp0c7bQiyVdxLinJGk_QBrs96xB8gL8ia_mzopGg4 GFIV4er.F.I6rl.6j2uvHX98l4MiNJXSvTE_uXHGsvzshZuwJGmqXwGx2w3e3fwpgpWjGnpbBP.n OWuH3io262RKB0ZXpDdEMzmQFC5KU5KVB6WAawZnq_OV2RVzU7X40MHobPHY9S4o3bdA9nqqMPU4 hnj4LLB0vV1z_KzgtI0mLHdt4xcpIImEwFU6qoNHWPT5LgdB4AZoeocnnLmBO0KfdLYv1I9iobzS S0JOLtnRPCrQviiGGijQwv8FvI2uHcz_qXsChJdqeF7Sp0y3._w9m_dJmMbfDB58lyoQvsF4puTS CJ5PuBtwm Received: from sonic.gate.mail.ne1.yahoo.com by sonic311.consmr.mail.bf2.yahoo.com with HTTP; Sun, 21 Jul 2019 14:56:07 +0000 Date: Sun, 21 Jul 2019 14:56:07 +0000 (UTC) From: Karen Goh To: ronljohnsonjr@gmail.com Cc: pgsql-admin@lists.postgresql.org Message-ID: <5479062.2668176.1563720967227@mail.yahoo.com> In-Reply-To: <34175669.2618496.1563675619671@mail.yahoo.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> <337537607.2613319.1563674408215@mail.yahoo.com> <34175669.2618496.1563675619671@mail.yahoo.com> Subject: Fw: 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_2668175_1981656681.1563720967226" Content-Length: 12848 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2668175_1981656681.1563720967226 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit Hi Ron, I am writing to you again regarding your earlier suggestion to create a surrogate key in my tutor_subject so that I can reference it to my s_tutor table tutor_id. My problem now is if I set the surrogate key as primary key, will the database recognize that it is used for referencing the tutor_id from table - s_tutor ? And since this tutor_id in tutor_subject can have more than 1 same tutor_id. How do I make that happened ? Hope to hear your opinion. Thanks. ----- Forwarded Message ----- From: Karen Goh To: "pgsql-admin@lists.postgresql.org" ; Ron Sent: Sunday, July 21, 2019, 10:20:19 AM GMT+8Subject: 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, 10:08:16 AM GMT+8, Ron wrote: On 7/20/19 9:00 PM, Karen Goh wrote: > > 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? Only you know your data and use cases. Is there another column you can add to make it a compound PK? Or create a synthetic key? Thanks Ron for the advice. I will google and learn what is compound PK and synthetic Key. It's a long time I do database query......and what you have suggested is new to me. Thanks so much for your replies !!! > > > > > 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. > > -- Angular momentum makes the world go 'round. ------=_Part_2668175_1981656681.1563720967226 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Hi Ron,

I am writing to you again regarding= your earlier suggestion to create a surrogate key in my tutor_subject so t= hat I can reference it to my s_tutor table tutor_id.

My problem now = is if I set the surrogate key as primary key, will the database recognize t= hat it is used for referencing the tutor_id from table - s_tutor ? And sinc= e this tutor_id in tutor_subject can have more than 1 same tutor_id.
How do I make that happened ?

Hope to hear your opinion.

Tha= nks.
=
----- Fo= rwarded Message -----
From: Karen Goh <= karenworld@yahoo.com>
To: "pgsql-admin@lists.postgresql= .org" <pgsql-admin@lists.postgresql.org>; Ron <ronljohnsonjr@gmail= .com>
Sent: Sunday, July 21, 2019, 10:20:19 AM GMT+8
Subject: Re: How do I alter an existing column and add a for= eign key which is a Primary key to a table?

=





On Sunday, July 21, 2019, 10:08:16 AM GMT+8, Ron <ronljohnsonjr@g= mail.com> wrote:


On 7/20/19 9:00 PM, Karen Goh wrote:
>
> 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:<= br clear=3D"none">> >
> > 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 scrip= t 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 t= hat 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.
> > ><= br clear=3D"none">> > > So, meaning I need to create a foreign key= as well as primary key for
> > tutor_id.
> > >
> > > So far, this is w= hat 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 tuto= r_id already exist in tutor_subject?
> >
> > Yes. It is already there but it is the first time I use= d 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)
> >
&= gt; > But, am receiving error messagte :
> >
> > ERROR: could not create unique index "tutor_subjec= t_pk"
> > DETAIL: Key (tutor_id)=3D(0) is duplicate= d.
> > SQL state: 23505
> >=
> > I noticed several of the rows has 0 at tutor_i= d. 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 duplication
> becuase of = my use case.
> Basically, tutor_subject is a 'JOIN' ta= ble so it will have duplicate
> tutor_id as it is a ma= ny-to-many relationship design.
>
&g= t; In this case, what should I do then since I can't make tutor_id a Primar= y
> key but yet it has to reference s_tutor.tutor_id a= s foreign key?

Only you know your data= and use cases. Is there another column you can add
to m= ake it a compound PK? Or create a synthetic key?

Thanks Ron for the advice. I will google and learn what is comp= ound PK and synthetic Key.
It's a long time I do database= query......and what you have suggested is new to me.
Thanks so much for your replies !!!


>
> >
> > Wh= at 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 tu= tor_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.
>
>
> >
> > Ha= ve you read the documentation?
> > https://www.post= gresql.org/docs/9.6/sql-altertable.html
> > http://= www.postgresqltutorial.com/postgresql-primary-key/
> &= gt; http://www.postgresqltutorial.com/postgresql-foreign-key/
> >
> >
> > --> > Angular momentum makes the world go 'round.
> >
> >
> = >
>
> --
>= ; Angular momentum makes the world go 'round.
>
>

--
Angular momentum makes the world go 'round.


------=_Part_2668175_1981656681.1563720967226--