Subject: Re: How do I alter an existing column and add a foreign key which is
 a Primary key to a table?
To: pgsql-admin@lists.postgresql.org
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>
From: Ron <ronljohnsonjr@gmail.com>
Message-ID: <e381b0d6-f838-6c71-c95a-fd580a19a211@gmail.com>
Date: Sat, 20 Jul 2019 21:07:48 -0500
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.7.2
MIME-Version: 1.0
In-Reply-To: <337537607.2613319.1563674408215@mail.yahoo.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit
Content-Language: en-US
Precedence: bulk

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:
> >
> > 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?
>
> 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?

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