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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

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

Karen Goh
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 <[hidden email]>
Sent: Sunday, July 21, 2019, 11:37:34 PM GMT+8
Subject: 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 <[hidden email]>
> 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.


Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Sunday, July 21, 2019, Karen Goh <[hidden email]> 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.