Re: How do I alter an existing column and add a foreign key which isa Primary key to a table? - Mailing list pgsql-admin

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