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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 7/20/19 8:31 PM, Karen Goh wrote:

On Sunday, July 21, 2019, 9:25:54 AM GMT+8, Ron <ronljohnsonjr@xxxxxxxxx> 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.



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.





[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux