Oracle: using foreign keys in object relation database

Question

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?

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?

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;

the very simple select statement i am trying to run:

select * from Course_objtab;

I don't know why you're trying to insert REF(D) etc., but you should just be using regular values AFAIK, from what you seem to be trying to do. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jun 6, 2016 at 5:01

dcieslak · Accepted Answer · 2016-06-06 05:29:47Z

1

The below works fine but I am not sure if you are looking for that.

CREATE TYPE Course_objtyp AS OBJECT (
  CourseName VARCHAR(20),
  CourseID NUMBER,
  DeptID Number,
  TeacherID Number
  );

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

INSERT INTO Course_objtab
 SELECT 'Chem101',001, D.DeptID, T.TeacherID
 FROM Department_objtab D, Teacher_objtab T
 WHERE D.DeptID = 1111 and T.TeacherID = 1;

answered Jun 6, 2016 at 5:29

dcieslak

2,7151 gold badge15 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle: using foreign keys in object relation database

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related