Re: How do I alter an existing column and add a foreign key which isa Primary key to a table? - Mailing list pgsql-admin
From | Ron |
---|---|
Subject | Re: How do I alter an existing column and add a foreign key which isa Primary key to a table? |
Date | |
Msg-id | e381b0d6-f838-6c71-c95a-fd580a19a211@gmail.com Whole thread Raw |
In response to | Re: How do I alter an existing column and add a foreign key whichis a Primary key to a table? (Karen Goh <karenworld@yahoo.com>) |
Responses |
Re: How do I alter an existing column and add a foreign key whichis a Primary key to a table?
(Karen Goh <karenworld@yahoo.com>)
|
List | pgsql-admin |
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.
pgsql-admin by date: