agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow do I alter an existing column and add a foreign key which is a Primary key to a table?
26+ messages / 6 participants
[nested] [flat]
* How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 00:58 Karen Goh <karenworld@yahoo.com>
0 siblings, 2 replies; 26+ messages in thread
From: Karen Goh @ 2019-07-21 00:58 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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)
Please help me on this.
Thanks.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 01:25 Ron <ronljohnsonjr@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
1 sibling, 1 reply; 26+ messages in thread
From: Ron @ 2019-07-21 01:25 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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?
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 01:31 Karen Goh <karenworld@yahoo.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 01:31 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
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?
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.
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 01:48 Ron <ronljohnsonjr@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 1 reply; 26+ messages in thread
From: Ron @ 2019-07-21 01:48 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 02:00 Karen Goh <karenworld@yahoo.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Karen Goh @ 2019-07-21 02:00 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 02:07 Ron <ronljohnsonjr@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
1 sibling, 1 reply; 26+ messages in thread
From: Ron @ 2019-07-21 02:07 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 02:20 Karen Goh <karenworld@yahoo.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 02:20 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
On Sunday, July 21, 2019, 10:08:16 AM GMT+8, Ron <ronljohnsonjr@gmail.com> wrote:
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?
Thanks Ron for the advice. I will google and learn what is compound PK and synthetic Key.
It's a long time I do database query......and what you have suggested is new to me.
Thanks so much for your replies !!!
>
> >
> > 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 02:48 David G. Johnston <david.g.johnston@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
1 sibling, 0 replies; 26+ messages in thread
From: David G. Johnston @ 2019-07-21 02:48 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Ron <ronljohnsonjr@gmail.com>
On Saturday, July 20, 2019, Karen Goh <karenworld@yahoo.com> wrote:
>
> > > 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.
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 07:28 Holger Jakobs <holger@jakobs.com>
parent: Karen Goh <karenworld@yahoo.com>
1 sibling, 1 reply; 26+ messages in thread
From: Holger Jakobs @ 2019-07-21 07:28 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org,Karen Goh <karenworld@yahoo.com>
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 and their JOIN operations. This is a common misconception.
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 13:53 Karen Goh <karenworld@yahoo.com>
parent: Holger Jakobs <holger@jakobs.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 13:53 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Holger Jakobs <holger@jakobs.com>
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>:
>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 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 ?
Regards,
Holger
--
Holger Jakobs, Bergisch Gladbach
+49 178 9759012
- sent from mobile, therefore short -
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 14:12 Ron <ronljohnsonjr@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 1 reply; 26+ messages in thread
From: Ron @ 2019-07-21 14:12 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 14:24 Karen Goh <karenworld@yahoo.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 14:24 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
On Sunday, July 21, 2019, 10:12:33 PM GMT+8, Ron <ronljohnsonjr@gmail.com> wrote:
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';
Hi Ron,
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.
--
Angular momentum makes the world go 'round.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 14:54 David G. Johnston <david.g.johnston@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 1 reply; 26+ messages in thread
From: David G. Johnston @ 2019-07-21 14:54 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Ron <ronljohnsonjr@gmail.com>
On Sunday, July 21, 2019, Karen Goh <karenworld@yahoo.com> wrote:
> 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.
>
>
You need to show your entire schema and the queries that are giving you
trouble if you want to get help that is less confusing. Though as I wrote
before I think your immediate problem is that you don’t have a “tutor”
table on which to create the PK.
David J.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Fw: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 14:56 Karen Goh <karenworld@yahoo.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 0 replies; 26+ messages in thread
From: Karen Goh @ 2019-07-21 14:56 UTC (permalink / raw)
To: ronljohnsonjr@gmail.com; +Cc: pgsql-admin@lists.postgresql.org
Hi Ron,
I am writing to you again regarding your earlier suggestion to create a surrogate key in my tutor_subject so that I can reference it to my s_tutor table tutor_id.
My problem now is if I set the surrogate key as primary key, will the database recognize that it is used for referencing the tutor_id from table - s_tutor ? And since this tutor_id in tutor_subject can have more than 1 same tutor_id.
How do I make that happened ?
Hope to hear your opinion.
Thanks. ----- Forwarded Message ----- From: Karen Goh <karenworld@yahoo.com>To: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>; Ron <ronljohnsonjr@gmail.com>Sent: Sunday, July 21, 2019, 10:20:19 AM GMT+8Subject: Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
On Sunday, July 21, 2019, 10:08:16 AM GMT+8, Ron <ronljohnsonjr@gmail.com> wrote:
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?
Thanks Ron for the advice. I will google and learn what is compound PK and synthetic Key.
It's a long time I do database query......and what you have suggested is new to me.
Thanks so much for your replies !!!
>
> >
> > 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:00 Karen Goh <karenworld@yahoo.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 15:00 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
On Sunday, July 21, 2019, 10:55:13 PM GMT+8, David G. Johnston <david.g.johnston@gmail.com> wrote:
On Sunday, July 21, 2019, Karen Goh <karenworld@yahoo.com> wrote:
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.
You need to show your entire schema and the queries that are giving you trouble if you want to get help that is less confusing. Though as I wrote before I think your immediate problem is that you don’t have a “tutor” table on which to create the PK.
Is there a way for me to generate an entity table diagram vis pgAdmin4 or PSQL ?
David J.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:06 David G. Johnston <david.g.johnston@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 1 reply; 26+ messages in thread
From: David G. Johnston @ 2019-07-21 15:06 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>; Ron <ronljohnsonjr@gmail.com>
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
David J.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:10 Karen Goh <karenworld@yahoo.com>
parent: David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 15:10 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org, Ron <ronljohnsonjr@gmail.com>
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
David J.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:13 Ron <ronljohnsonjr@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 1 reply; 26+ messages in thread
From: Ron @ 2019-07-21 15:13 UTC (permalink / raw)
To: ; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:23 Karen Goh <karenworld@yahoo.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 2 replies; 26+ messages in thread
From: Karen Goh @ 2019-07-21 15:23 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:37 Ron <ronljohnsonjr@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
1 sibling, 1 reply; 26+ messages in thread
From: Ron @ 2019-07-21 15:37 UTC (permalink / raw)
To: ; +Cc: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Fw: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 15:44 Karen Goh <karenworld@yahoo.com>
parent: Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 15:44 UTC (permalink / raw)
To: pgsql-sql@lists.postgresql.org; +Cc: ronljohnsonjr@gmail.com
Hope that this is the right place to ask about this regarding how Ron suggested to do a Select something when it is not inside a table at all. He said this is something basic. So hope someone could clarify.
Thanks
----- Forwarded Message ----- From: Ron <ronljohnsonjr@gmail.com>Cc: "pgsql-admin@lists.postgresql.org" <pgsql-admin@lists.postgresql.org>Sent: Sunday, July 21, 2019, 11:37:34 PM GMT+8Subject: Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 16:10 Avin Kavish <avinkavish@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
1 sibling, 2 replies; 26+ messages in thread
From: Avin Kavish @ 2019-07-21 16:10 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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
<https://stackoverflow.com/help/minimal-reproducible-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.
>
>
>
>
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 16:26 Karen Goh <karenworld@yahoo.com>
parent: Avin Kavish <avinkavish@gmail.com>
1 sibling, 0 replies; 26+ messages in thread
From: Karen Goh @ 2019-07-21 16:26 UTC (permalink / raw)
To: Avin Kavish <avinkavish@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Sent from Yahoo Mail for iPhone
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 reallyimportant 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 16:30 Karen Goh <karenworld@yahoo.com>
parent: Avin Kavish <avinkavish@gmail.com>
1 sibling, 1 reply; 26+ messages in thread
From: Karen Goh @ 2019-07-21 16:30 UTC (permalink / raw)
To: Avin Kavish <avinkavish@gmail.com>; +Cc: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
Sent from Yahoo Mail for iPhone
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 reallyimportant 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.
^ permalink raw reply [nested|flat] 26+ messages in thread
* Re: How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-21 17:30 Scott Ribe <scott_ribe@elevated-dev.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 0 replies; 26+ messages in thread
From: Scott Ribe @ 2019-07-21 17:30 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: Avin Kavish <avinkavish@gmail.com>; Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org
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 been writing queries for a long time, but your questions here demontrate a serious lack of understanding of the basics. My personal 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/
^ permalink raw reply [nested|flat] 26+ messages in thread
* How do I alter an existing column and add a foreign key which is a Primary key to a table?
@ 2019-07-22 03:14 David G. Johnston <david.g.johnston@gmail.com>
parent: Karen Goh <karenworld@yahoo.com>
0 siblings, 0 replies; 26+ messages in thread
From: David G. Johnston @ 2019-07-22 03:14 UTC (permalink / raw)
To: Karen Goh <karenworld@yahoo.com>; +Cc: pgsql-sql@lists.postgresql.org <pgsql-sql@lists.postgresql.org>; ronljohnsonjr@gmail.com <ronljohnsonjr@gmail.com>
On Sunday, July 21, 2019, Karen Goh <karenworld@yahoo.com> wrote:
> Hope that this is the right place to ask about this regarding how Ron
> suggested to do a Select something when it is not inside a table at all. He
> said this is something basic.
>
As noted again below you seems to have missed the meaning of:
FROM tutor_subject, s_tutor
The meaning of that is indeed fundamental SQL; if you simply missed it that
happens but if you haven’t learned that you should, as advised, elsewhere,
read or re-read introductory materials that teach “FROM tbl1, tbl2” joins
amongst other concepts that at worse could probably use a refresher.
So hope someone could clarify.
>
If you continue to learn sql via mailing list can you please at least
follow the conventions others here have been using. Namely to quote only
the relevant portions of the text you are replying to and to add your
comments for the reply AFTER the material you quoted, NOT BEFORE. I.e.,
Bottom-post instead of Top-post. It also seems like your mail client isn’t
distinguishing the quoted material from your reply. Having it do that
better would be a big help too.
The suggestion to use this list instead of the admin list has been
adequately explained elsewhere.
On to the point here: You’ve provided a model but still haven’t told us, in
your own prose (i.e., not SQL) what it is you are trying to accomplish
here. You have a TUTOR table (not sure why you went with an s_ prefix) and
a tutor_subject table. You are lacking a SUBJECT table where subject_id is
the PK. You then lack stated questions that those three tables are capable
of answering.
As for Ron’s query, it is correct but regardless you still really have not
told us what you are trying to do, show us what you did, and explain in
detail what parts specifically are confusing between your attempt and your
expectations and you will get good help. Continue to be vague and the
answers you get will either be confusing, unhelpful, or wrong as you’ve
experienced.
The canonical way to obtain the answer to the query: provide me a list of
tutors, along with the zipcodes, who tutors the subject X, listing each
tutor only once.
SELECT s_tutor.tutor_id, s_tutor.zipcode
FROM s_tutor
WHERE EXISTS (
SELECT 1
FROM tutor_subject
WHERE tutor_subject.tutor_id = s_tutor.tutor_id
AND tutor_subject.subject_id = ‘X’
)
Using an actual join, as Ron, did requires (in addition to fixing his typo)
removing the duplicates that are introduced due to the join on
tutor_subject.
SELECT DISTINCT tutor_id, s_tutor.zipcode
FROM s_tutor
JOIN tutor_subject USING (tutor_id) — personally learn and use the more
expressive join clauses instead of shoving all of the relations into FROM
and polluting the WHERE clause with join conditions; and apparently in. the
“FROM tutor_subject, s_tutor” expression that Ron wrote you either didn’t
see the “, s_tutor” or understand what it meant. It, combined with “WHERE
(tutor_subject.tutor_id = s_tutor.tutor_id)” forms the exact same INNER
JOIN shown here (with the exception of this form only outputting tutor_id
once)
WHERE tutor_subject.subject_id = ‘X’;
The DISTINCT is actually not required for a single subject because a single
tutor_id can only be linked to a given subject_id once so no duplicates
will appear. But if there is more than one subject_id a single tutor that
teaches both will generate two output matches when you probably only want
the one (again, I’m making an assumption here because you have not stated
your question).
A more useful way to accomplish the above in the face of multiple
subject_id values is:
SELECT s_tutor.tutor_id, s_tutor.zipcode, array_agg(tutor_subject.subject_id)
AS matched_subjects
FROM s_tutor
JOIN tutor_subject USING (tutor_id)
WHERE tutor_subject.subject_id IN (‘X’,’Y’,’Z’)
GROUP BY 1, 2;
That only matters if you wish to know which conditions matched; otherwise
the simple DISTINCT works or, preferred, the semi-join/EXISTS form of the
query so only s_tutor records are returned regardless of how many matching
records EXISTS in the subquery containing tutor_subject).
Tutor_subject: FOREIGN KEY (tutor_id) REFERENCES s_tutor (tutor_id)
A given zipcode matches with a given tutor_id on the tutor_subject table
because that tutor_id IS THE EXACT SAME ID as is found on s_tutor. That
said it only works walking from tutor_subject since two different tutor_id
values can have the same zipcode but a single tutor_id can only have one
zip code (i.e., many-to-one relationship)
Your output query really wants to include tutor_id, outputting zipcode by
itself would largely be pointless though I could see using it as a grouping
key and showing count(tutor_id)...
David J.
^ permalink raw reply [nested|flat] 26+ messages in thread
end of thread, other threads:[~2019-07-22 03:14 UTC | newest]
Thread overview: 26+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-07-21 00:58 How do I alter an existing column and add a foreign key which is a Primary key to a table? Karen Goh <karenworld@yahoo.com>
2019-07-21 01:25 ` Ron <ronljohnsonjr@gmail.com>
2019-07-21 01:31 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 01:48 ` Ron <ronljohnsonjr@gmail.com>
2019-07-21 02:00 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 02:07 ` Ron <ronljohnsonjr@gmail.com>
2019-07-21 02:20 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 14:56 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 02:48 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-21 07:28 ` Holger Jakobs <holger@jakobs.com>
2019-07-21 13:53 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 14:12 ` Ron <ronljohnsonjr@gmail.com>
2019-07-21 14:24 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 14:54 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-21 15:00 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 15:06 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-21 15:10 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 15:13 ` Ron <ronljohnsonjr@gmail.com>
2019-07-21 15:23 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 15:37 ` Ron <ronljohnsonjr@gmail.com>
2019-07-21 15:44 ` Karen Goh <karenworld@yahoo.com>
2019-07-22 03:14 ` David G. Johnston <david.g.johnston@gmail.com>
2019-07-21 16:10 ` Avin Kavish <avinkavish@gmail.com>
2019-07-21 16:26 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 16:30 ` Karen Goh <karenworld@yahoo.com>
2019-07-21 17:30 ` Scott Ribe <scott_ribe@elevated-dev.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox