Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Creating a relationship between 2 tables

Re: Creating a relationship between 2 tables

From: Ed Prochak <edprochak_at_gmail.com>
Date: 20 Jul 2006 07:23:39 -0700
Message-ID: <1153405418.981300.175970@s13g2000cwa.googlegroups.com>

Andyza wrote:
> I'm running Oracle 9i on Windows XP with TOAD as a "front-end" to
> Oracle.
>
> Sample database structure:
>
> STUDENTS {
> SYSTEMID (PK),
> STUDENTID,
> NAME,
> SURNAME,
> IDNUMBER
> }
>
> COURSESDONE {
> COURSEID (PK),
> COURSENAME,
> RESULT,
> STUDENTID
> }
>
> Currently the tables aren't related as no relationships between the two
> tables have been defined. I'd like to add the SYSTEMID column to the
> COURSESDONE table and add a foreign key relationship between the two
> tables, i.e:
>
> ALTER TABLE COURSESDONE ADD (
> "SYSTEMID" VARCHAR2(50),
> CONSTRAINT "COURSESDONE_SYSTEMID_FK" FOREIGN KEY("SYSTEMID")
> REFERENCES STUDENTS("SYSTEMID")
> )
>
> That's fine... The tricky part is that I also want to copy the existing
> data from the SYSTEMID column on the STUDENTS table to the new SYSTEMID
> column on the COURSESDONE table.
>
> Each student only appears on the STUDENTS table once but each student
> can have many records on the COURSESDONE table (1:M).
>
> How do I get the SYSTEMID data into the COURSESDONE table? I suppose
> I'd have to loop through each of the STUDENTS records and check if the
> student in each record has a matching COURSESDONE.STUDENTID record and
> then update the COURSESDONE.SYSTEMID value if any matches are found. Is
> this correct? If so, what's the syntax that I can run from TOAD or SQL
> Navigator?

You design is horrible. Why is SYSTEMID the PK for students and not STUDENTID? You have gone pseudokey happy. This looks like a homework assignment, so you should post sample code and we can try to help. But we will not solve your homework for you. Look up the UPDATE statement. (BIG HINT you will use a version that includes a subquery).

post your attempt and we'll help move you along. (but meanwhile consider fixing your data model design!)

 Ed

. Received on Thu Jul 20 2006 - 09:23:39 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US