Creating a relationship between 2 tables

Andyza · *nix forums beginner Joined: 17 Feb 2005 Posts: 5

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?

Ed Prochak · *nix forums Guru Wannabe Joined: 17 Mar 2005 Posts: 271

Andyza wrote:

Andyza · *nix forums beginner Joined: 17 Feb 2005 Posts: 5

Google