Oracle: using foreign keys in object relation database
<p>I have a question about using primary keys and foreign keys in an object relational database in oracle. Below is my sql code and insert statements. I am trying to create a simple database with student, course, teacher and department. but whenever I run the below select statement, I do not see the DepartmentID and TeacherID that I entered. Instead i get [HR.DEPARTMENT_OBJTYP] as the value for departmentID and likewise for teacherID. is this because I have referenced the whole department rather than just the departmentID in the course table? I do not know how to use foreign keys and query them properly in an object relational database, and have not been able to find any useful easy to understand info on it. could someone please show me where I am going wrong?</p>

<p>for example in Course_objtyp I do... TeacherID REF Teacher_objtyp. is this the correct way to reference, I am not sure as i only want to reference the teacherID not the whole Teacher_objtyp?</p>

<pre><code>CREATE TYPE Student_objtyp AS OBJECT (
  FName         VARCHAR2(20),
  LName         VARCHAR2(20),
  StudentID     NUMBER
  ); 
/

CREATE TABLE Student_objtab OF Student_objtyp (StudentID PRIMARY KEY)
  OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TYPE Department_objtyp AS OBJECT (
  DeptID NUMBER,
  DeptName VARCHAR2(20)
  );
/  

CREATE TABLE Department_objtab OF Department_objtyp (DeptID PRIMARY KEY)
  OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TYPE Teacher_objtyp AS OBJECT (
  TeacherID NUMBER,
  FName VARCHAR2(20),
  LName VARCHAR2(20)
  );
/

CREATE TABLE Teacher_objtab OF Teacher_objtyp (TeacherID PRIMARY KEY)
  OBJECT IDENTIFIER IS PRIMARY KEY;

CREATE TYPE Course_objtyp AS OBJECT (
  CourseName VARCHAR(20),
  CourseID NUMBER,
  DeptID REF Department_objtyp,
  TeacherID REF Teacher_objtyp
  );
/

CREATE TABLE Course_objtab OF Course_objtyp (
  PRIMARY KEY (CourseID),
  FOREIGN KEY (DeptID) REFERENCES Department_objtab,
  FOREIGN KEY (TeacherID) REFERENCES Teacher_objtab)
/

INSERT INTO Student_objtab VALUES('bill','smitts',1);
INSERT INTO Student_objtab VALUES('bob','jo',2);

INSERT INTO Teacher_objtab VALUES(1,'Mr','Higgins');

INSERT INTO Department_objtab VALUES(1111,'Science');

INSERT INTO Course_objtab
SELECT 'Chem101',001,
REF(D),
REF(T)
FROM Department_objtab D, Teacher_objtab T
WHERE D.DeptID = 1111 and T.TeacherID = 1;
</code></pre>

<p>the very simple select statement i am trying to run:</p>

<pre><code>select * from Course_objtab;
</code></pre>