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 -> Creating a relationship between 2 tables

Creating a relationship between 2 tables

From: Andyza <andyza_at_webmail.co.za>
Date: 20 Jul 2006 06:11:21 -0700
Message-ID: <1153401081.689981.54420@h48g2000cwc.googlegroups.com>


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? Received on Thu Jul 20 2006 - 08:11:21 CDT

Original text of this message

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