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 1hp1Sh-0001EK-8v for pgsql-admin@arkaria.postgresql.org; Sun, 21 Jul 2019 02:20:31 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hp1Sf-0003HA-Nf for pgsql-admin@arkaria.postgresql.org; Sun, 21 Jul 2019 02:20:29 +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 1hp1Sf-0003H3-ER for pgsql-admin@lists.postgresql.org; Sun, 21 Jul 2019 02:20:29 +0000 Received: from sonic309-15.consmr.mail.bf2.yahoo.com ([74.6.129.125]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hp1Sc-0006B2-34 for pgsql-admin@lists.postgresql.org; Sun, 21 Jul 2019 02:20:29 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1563675623; bh=Q/AlwLZCU22fVAJsLm4xhJ+IC6/93Jyar1vHKbZNSCo=; h=Date:From:To:In-Reply-To:References:Subject:From:Subject; b=YzxHgiiZGx0dibbpJ1ivPxD39PNiSNH5ukKDxLR7TgZzDkFVzZY2gkg00kDCBOewWDh9LadsK1ggdKmaYX8KjVZcyE+kRzEc8F0IJf5+LafBtMACiy2e7wiTL/5iviKHSOoDsKYPUcqXt5WIDIfqdyWGVdZ+d8q37eCZD8brPNKk80X/b6jsHCqscnZ6T0PDbCphehkCrhtHWAAZY63Ixvg4+6kfJCK80ihB+Y3XA8ySg14HvnxoSAwFSamIvGI2sC0KspJzJZpFOy/34mq9jQXwfyKnz2bEPBG8uQFfQXUMFTloaWEG/iIOo62+oI2GEWXKBVtoUs6uTWtk4TuCgw== X-YMail-OSG: 9c53p4oVM1nWoFHgO0yZUUbw0kxiLAtJyZCo7BmutN0h_5iOQNZQNu91.9jOClU c9U5G1N8zpHS2PQJP2Shp41eMO.BXbCrBJF.7SbNHSqIvzxxKIOynrB3.Ze7rYEDfk2RTZL7FLUo K4YtgZTD5ndYO.Xvh3QIUAAlc8MQ3y5qMy6dOpScmzjIRshE75RAAsUPtnzQfgouTJyImKZt2k7x F_Oembsw1lvZzVkz44AdVhaOKU.90tHvKX_czzJ9Jh3Mq2y.CNCOXePFZxC6QOofAAYT7ucXjUyu kszuVOXsdRWhykqCFB0z9kTNdd76hRqOV3WEwaFNtcXgdikaBfTq.ZU9zcTIQPVMTp348tFdiI71 hheaWr5rSvfQdSxJQGEIYFRVqB2tLlPLuwO2H1b2Mg8gtAZDIrvwCzwJRLgl4mhWvLHy1TJipw2V gddlatURequVS1dQcvL6TESxq0mjUxp7kpNy5BE2Yq2zeWP7Y7zPHcrGRE8FV2LvtjScJqvcBx74 cFF9k9Lct3v5pjGF2R8gi17vnGvVUcQH5FQWvR3aO5L.GLTSmXjoeemtMZX3v3sBrfvssoJGp9s8 A8wBiXVyHgohRUsvYRPjfuRp0Rk_fGPYUeXHQr9MDGFhDvtBQcft0zCLuJB8zu4_L_SCYtISG_SK JKNvQqeHrYWCo9oNZpLOccF0.j2gn_qmFtYQoLkH38wFbk4MmyNMFa.sz5yVLhxuIMjbHGzvCozk 041XgUFZtbscnbSrsVX2VT00DjyrLcHpCvtQqZRf40K3y4LbNpB4k.iJe0corlRhQsUSs8H6uRI9 1hGqWVKbSEw0oAFCiBClTSQxbfcM1Qi8aIjbeiQhUYc8onEM_YI4J811gRzrT_aaDWvSDzVvfWJr YR4T9RQKg2Q2TYD9wsA9CvcQ_HV7DVJ3rw4CHCR6KVirch4s6zzKoFKcFo5Lo4QZEX_ABFeffsgp MXsqy1lfbSAmrGEkZhJd2ZGBCHm_uhpkyHT7P3HH.d6pv7tFA6av_S0DxSwp7n4lcqS6JWfKBh5N pW0fjj9dKhW8MGd5ojcdndD__Lr85LBxOHMRWuOx3_8zyS_OKu2CIz4T6l5O9iXMpTa9jDI1O2Y4 ob4YOTNDSLxryr9NQunWd3Y9PBFUC23Es86dQXfjI5SUsv4xBofKcIGo7I2g5Q9hNC2ke5Ruh19n Ysk3gkxa9QrBhqSZFkbofoGh4lD7f.03Th548c3R7WXrXn0G_5U0ZEt6.aZtq6F2BqQXbwrENHhi ZOz0eOFdqc8yjLh.t8gHd1SAHztrF85hg Received: from sonic.gate.mail.ne1.yahoo.com by sonic309.consmr.mail.bf2.yahoo.com with HTTP; Sun, 21 Jul 2019 02:20:23 +0000 Date: Sun, 21 Jul 2019 02:20:19 +0000 (UTC) From: Karen Goh To: pgsql-admin@lists.postgresql.org, Ron Message-ID: <34175669.2618496.1563675619671@mail.yahoo.com> In-Reply-To: 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> 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_2618495_437858425.1563675619669" Content-Length: 8594 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_2618495_437858425.1563675619669 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 7bit 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_2618495_437858425.1563675619669 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable





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


On 7/20/19= 9:00 PM, Karen Goh wrote:
>
> On Sunday, July 21, 2019, 9:49:1= 3 AM GMT+8, Ron <ronljohnsonjr@gmail.com>
> wrote:
>
&= gt;
> 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:
> >
> >
> > On 7/20/19 = 7:58 PM, Karen Goh wrote:
> >
> > > Hi all,
> &g= t; >
> > > I used to write a script in MYSQL and foreign and= primary key will be
> > created.
> > >
> > &= gt; With PG4Admin, I am lost.
> > >
> > > I realise= d 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
> &= gt; to have foreign key to a tutor_id which is also the primary key of that=
> table.
> > >
> > > So, meaning I need to c= reate a foreign key as well as primary key for
> > tutor_id.
&g= t; > >
> > > So far, this is what I have attempted but it= is not working.
> > > ALTER TABLE tutor_subject
> > &= gt; ADD CONSTRAINT tutor_subject_pk
> > > PRIMARY KEY (tutor_id= )
> > > ADD CONSTRAINT tutor_subject_fk
> > > FOREI= GN 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 pu= t 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)=3D(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 r= ows?
>
> Naturally. You can't have a unique index with dupl= icate keys.
>
> Sorry Ron. I just realised that my tutor_id nee= ds 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 t= his 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 synthe= tic 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 d= on't see that referenced in
> > your example.)
> >> > The foreign table will be s_tutor which has a tutor_id as well.<= br>> >
> > So, the tutor_id in tutor_subject will be both pr= imary 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.postgresqltutoria= l.com/postgresql-primary-key/
> > http://www.postgresqltutorial.co= m/postgresql-foreign-key/
> >
> >
> > --
>= > Angular momentum makes the world go 'round.
> >
> = >
> >
>
> --
> Angular momentum makes the wor= ld go 'round.
>
>

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


=20 ------=_Part_2618495_437858425.1563675619669--