Обсуждение: How do I alter an existing column and add a foreign key which is aPrimary key to a table?

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
theprimary 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)

Please help me on this.

Thanks.

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

What foreign table are you referencing?  (I don't see that referenced in 
your example.)

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.

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.


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

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.

> > I realised now that the keys are not created and perhaps that is why the
> join query is not working out.

The presence or absence of PK and FK meta data is not the problem here.  A properly written query will work whether those are present or not.  What is does indicate is a failure to understand the query, model, or both.  You should probably show more of both to receive better help.

>> 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 is this s_tutor thing you’ve newly added to the problem space?

Your tutor_id PK exists on the TUTOR TABLE.  Your tutor_subject.tutor_id FKs to that.  Your SUBJECT TABLE has a subject_id PK.  Your tutor_subject.subject_id field FKs to that.  If you need something to FK to the tutor_subject table it should also contain both tutor_id and subject_id fields (yes, you can create a make believe tutor_subject_id PK field and link to that - you shouldn’t).

If you need an FK for tutor_id its point to the TUTOR TABLE.  If you don’t have a TUTOR TABLE you’ve discovered the flaw in your model.

David J.

Am 21. Juli 2019 02:58:05 MESZ schrieb Karen Goh <karenworld@yahoo.com>:
>Hi all,
>
>With PG4Admin, I am lost.

Since pgadmin4 is just a frontend, you're not "lost with pgadmin4", but maybe with postgresql.

>I realised now that the keys are not created and perhaps that is why
>the join query is not working out.

Creating a foreign key doesn't magically create the necessary keys for it.

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

This looks like a 1:1 relationship which is to be avoided.

One ALTER TABLE command either adds a primary or a foreign key. There is no FK without the keyword REFERENCES.

FK relationships help in keeping the data consistent, but they are totally unrelated to/useless for SELECT statements
andtheir JOIN operations. This is a common misconception. 

Regards,

Holger

--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -

On 7/21/19 8:53 AM, Karen Goh wrote:
>
> On Sunday, July 21, 2019, 3:28:10 PM GMT+8, Holger Jakobs 
> <holger@jakobs.com> wrote:
>
> Am 21. Juli 2019 02:58:05 MESZ schrieb Karen Goh <karenworld@yahoo.com>:
>
[snip]
>
> This looks like a 1:1 relationship which is to be avoided.
>
> One ALTER TABLE command either adds a primary or a foreign key. There is 
> no FK without the keyword REFERENCES.
>
> FK relationships help in keeping the data consistent, but they are totally 
> unrelated to/useless for SELECT statements and their JOIN operations. This 
> is a common misconception.
>
> Hi Holger,
>
> After reading your reply, I am confused now.
>
> Cos without creating foreign key in my tutor_subject how am I going to 
> retrieve the zipcode at s_tutor table that meet the list of subject_name 
> and tutor_id in tutor_subject ? There should have some reference right ? 
> If not, how does the database tell this zipcode belong to which tutor_id ?

The reference is in the SELECT statement, not in the FK definition.

SELECT s_tutor.zip_code
from subject_tutor, s_tutor
where s_tutor.tutor_id = subject_tutor.tutor_id
and subject_tutor.subject_name = 'Angela Merkel';

-- 
Angular momentum makes the world go 'round.

You can't Select s_tutor.zip_code from subject_tutor as zip_code belongs to s_tutor table.

Hence, I wrote my last message about my confusion.

Is there a way for me to generate an entity table diagram vis pgAdmin4 or PSQL ?

On 7/21/19 10:10 AM, Karen Goh wrote:
>
> On Sunday, July 21, 2019, 11:06:16 PM GMT+8, David G. Johnston 
> <david.g.johnston@gmail.com> wrote:
>
>
> On Sunday, July 21, 2019, Karen Goh <karenworld@yahoo.com> wrote:
>
>
> Is there a way for me to generate an entity table diagram vis pgAdmin4 or 
> PSQL ?
>
>
> Just write the 5 or so tables out in a somewhat compressed format. Psql 
> doesn’t and I don’t know about pgadmin
>
> Here is a compressed format :
>
> s_tutor
> --------
> zipcode(text)
> tutor_id (I just checked this is becomes a 'serial id ?' based on the 
> generated sql
>
> ALTER TABLE public.s_tutor ALTER COLUMN tutor_id ADD GENERATED BY DEFAULT 
> AS IDENTITY (
> SEQUENCE NAME public.tutor_strtutorid_seq
> START WITH 1
> INCREMENT BY 1
> NO MINVALUE
> NO MAXVALUE
> CACHE 1
> );
>
> tutor_subject
> ----------------
> tutor_id
> subject_Names

select s_tutor.zipcode
from s_tutor, tutor_subject
where s_tutor.tutor_id = tutor_subject.tutor_id
   and tutor_subject.subject_names ='Angela Merkel'


-- 
Angular momentum makes the world go 'round.

On 7/21/19 10:23 AM, Karen Goh wrote:
>
> On Sunday, July 21, 2019, 11:13:58 PM GMT+8, Ron <ronljohnsonjr@gmail.com> 
> wrote:
>
>
> On 7/21/19 10:10 AM, Karen Goh wrote:
[snip]
> >
> > Here is a compressed format :
> >
> > s_tutor
> > --------
> > zipcode(text)
> > tutor_id (I just checked this is becomes a 'serial id ?' based on the
> > generated sql
> >
> > ALTER TABLE public.s_tutor ALTER COLUMN tutor_id ADD GENERATED BY DEFAULT
> > AS IDENTITY (
> > SEQUENCE NAME public.tutor_strtutorid_seq
> > START WITH 1
> > INCREMENT BY 1
> > NO MINVALUE
> > NO MAXVALUE
> > CACHE 1
> > );
> >
> > tutor_subject
> > ----------------
> > tutor_id
> > subject_Names
>
>
> select s_tutor.zipcode
> from s_tutor, tutor_subject
> where s_tutor.tutor_id = tutor_subject.tutor_id
> and tutor_subject.subject_names ='Angela Merkel'
>
> Hi Ron,
>
> Could you explain why you select s_tutor.zipcode from tutor_subject since 
> there is no zipcode inside tutor_subject?
>

I did not do what you think I did.

> Perhaps my reasoning is wrong and I really want to have your expert view.

It might sound harsh, but is not meant to be: this list is for administering 
PostgreSQL, not teaching basic SQL.

-- 
Angular momentum makes the world go 'round.

On Monday, July 22, 2019, 12:10 AM, Avin Kavish <avinkavish@gmail.com> wrote:

Hey Karen,

That query is pretty much an inner join with a filter that selects a single value from the matching rows. 

It's picking the tutor's zipcode from the tutor table where any tutor is teaching a subject called "Angela Merkel". 

For this to happen, it needs to join the tutor subjects with the tutor details table using the tutor id as the common factor.

Perhaps you should try www.stackoverflow.com for SQL guidance. They might be able to explain in detail what you are doing (wrong). 

Make sure to provide a complete example showing all the involved relations and references and any queries your wrote. This is really

important or they downvote/close questions. (your original email is not sufficient detail)

This is the wrong forum so you need not reply cos Ron has mentioned already.

N also, why select zip code when it is not even supplied with a value?

If this is not the mailing list then why do PostgreSQL suggest user to subscribe this?

Regards,

Avin

On Sun, Jul 21, 2019 at 8:54 PM Karen Goh <karenworld@yahoo.com> wrote:

On Sunday, July 21, 2019, 11:13:58 PM GMT+8, Ron <ronljohnsonjr@gmail.com> wrote:


On 7/21/19 10:10 AM, Karen Goh wrote:

>
> On Sunday, July 21, 2019, 11:06:16 PM GMT+8, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>
>
> On Sunday, July 21, 2019, Karen Goh <karenworld@yahoo.com> wrote:
>
>
> Is there a way for me to generate an entity table diagram vis pgAdmin4 or
> PSQL ?
>
>
> Just write the 5 or so tables out in a somewhat compressed format. Psql
> doesn’t and I don’t know about pgadmin
>
> Here is a compressed format :
>
> s_tutor
> --------
> zipcode(text)
> tutor_id (I just checked this is becomes a 'serial id ?' based on the
> generated sql
>
> ALTER TABLE public.s_tutor ALTER COLUMN tutor_id ADD GENERATED BY DEFAULT
> AS IDENTITY (
> SEQUENCE NAME public.tutor_strtutorid_seq
> START WITH 1
> INCREMENT BY 1
> NO MINVALUE
> NO MAXVALUE
> CACHE 1
> );
>
> tutor_subject
> ----------------
> tutor_id
> subject_Names


select s_tutor.zipcode
from s_tutor, tutor_subject
where s_tutor.tutor_id = tutor_subject.tutor_id
and tutor_subject.subject_names ='Angela Merkel'

Hi Ron,

Could you explain why you select s_tutor.zipcode from tutor_subject since there is no zipcode inside tutor_subject?

Perhaps my reasoning is wrong and I really want to have your expert view.


--
Angular momentum makes the world go 'round.

On Monday, July 22, 2019, 12:10 AM, Avin Kavish <avinkavish@gmail.com> wrote:

Hey Karen,

That query is pretty much an inner join with a filter that selects a single value from the matching rows. 

It's picking the tutor's zipcode from the tutor table where any tutor is teaching a subject called "Angela Merkel". 

For this to happen, it needs to join the tutor subjects with the tutor details table using the tutor id as the common factor.

N I need to return tutor_id so it is terribly wrong !!! So, in addition to value not being supplied. 

Perhaps you should try www.stackoverflow.com for SQL guidance. They might be able to explain in detail what you are doing (wrong). 

Make sure to provide a complete example showing all the involved relations and references and any queries your wrote. This is really

important or they downvote/close questions. (your original email is not sufficient detail)

Regards,

Avin

On Sun, Jul 21, 2019 at 8:54 PM Karen Goh <karenworld@yahoo.com> wrote:

On Sunday, July 21, 2019, 11:13:58 PM GMT+8, Ron <ronljohnsonjr@gmail.com> wrote:


On 7/21/19 10:10 AM, Karen Goh wrote:

>
> On Sunday, July 21, 2019, 11:06:16 PM GMT+8, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>
>
> On Sunday, July 21, 2019, Karen Goh <karenworld@yahoo.com> wrote:
>
>
> Is there a way for me to generate an entity table diagram vis pgAdmin4 or
> PSQL ?
>
>
> Just write the 5 or so tables out in a somewhat compressed format. Psql
> doesn’t and I don’t know about pgadmin
>
> Here is a compressed format :
>
> s_tutor
> --------
> zipcode(text)
> tutor_id (I just checked this is becomes a 'serial id ?' based on the
> generated sql
>
> ALTER TABLE public.s_tutor ALTER COLUMN tutor_id ADD GENERATED BY DEFAULT
> AS IDENTITY (
> SEQUENCE NAME public.tutor_strtutorid_seq
> START WITH 1
> INCREMENT BY 1
> NO MINVALUE
> NO MAXVALUE
> CACHE 1
> );
>
> tutor_subject
> ----------------
> tutor_id
> subject_Names


select s_tutor.zipcode
from s_tutor, tutor_subject
where s_tutor.tutor_id = tutor_subject.tutor_id
and tutor_subject.subject_names ='Angela Merkel'

Hi Ron,

Could you explain why you select s_tutor.zipcode from tutor_subject since there is no zipcode inside tutor_subject?

Perhaps my reasoning is wrong and I really want to have your expert view.


--
Angular momentum makes the world go 'round.

This forum is recommended for questions regarding administration of PostgreSQL, which your questions are not related
to.pgsql-gernal or pgsql-sql would be better fits. 

But I'd strongly suggest that you pause and learn SQL basics in a structured way. You've said several times that you've
beenwriting queries for a long time, but your questions here demontrate a serious lack of understanding of the basics.
Mypersonal recommendation: 

https://www.postgresql.org/docs/11/tutorial-sql.html

Or, for a quicker start:

https://www.postgresql.org/docs/11/tutorial-sql.html
https://www.postgresql.org/docs/11/sql.html

--
Scott Ribe
scott_ribe@elevated-dev.com
https://www.linkedin.com/in/scottribe/