Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: Karen Goh <karenworld@yahoo.com>
To: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
Subject: Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
Date: Sun, 21 Jul 2019 02:00:08 +0000 (UTC)
Message-ID: <337537607.2613319.1563674408215@mail.yahoo.com> (raw)
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>

 




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?


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

view thread (26+ messages)  latest in thread

Message-ID: <337537607.2613319.1563674408215@mail.yahoo.com>
Permalink:  ../337537607.2613319.1563674408215@mail.yahoo.com/
Also on:    postgresql.org/message-id/337537607.2613319.1563674408215@mail.yahoo.com

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: karenworld@yahoo.com, ronljohnsonjr@gmail.com
  Subject: Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
  In-Reply-To: <337537607.2613319.1563674408215@mail.yahoo.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox