How do I define object-references between tables using the object-relational database feature of oracle SQL developer

Question

0.00/5 (No votes)

See more:

I successfully created an EmployeeType in Oracle 11g with the following atrributes;

emp_id VARCHAR2(5)
title VARCHAR(15)
firstname VARCHAR(20)
surname VARCHAR(20)
address Addresstype
tel_no ARRAY(3) of VARCHAR(15)
get_address() VARCHAR2()

and was told to create DepartmentType in Oracle 11g with the following attributes

dept_id VARCHAR2(3)
location AddressType
manager REF Employee

but my problem now is I don't know how to use the object-relational REF construct to define object-references between the two tables for relating Departments to Employees so that I can create table which will store department details and insert rows.

Posted 22-Feb-13 15:53pm

chidoskychidosky

Add a Solution

Comments

chidoskychidosky 22-Feb-13 22:08pm

I created the DepartmentType with the following command
CREATE TYPE DepartmentType as OBJECT (
dept_id VARCHAR2(3),
locations AddressType,
manager REF EmployeeType
)NOT FINAL;
but when I tried using the following command
INSERT INTO department VALUES
(D01, AddressType(47, 'Canal Street', 'Manchester', 'MR8 9WR'),
(SELECT REF(e) FROM EMPLOYEE e WHERE emp_id = 2)); to insert rows into the department table I got an error saying 'Column not allowed here'. I think the problem here is I have not related the Department table to the Employee table which am very confused on how to reference this two tables.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gvprabu · Answer 1 · 2013-02-22T17:33:00

Solution 1

Hi,

You need to create Foreign Key in Child Table (Employee) using Parent Table (Department).

Check the following Links...

SQL FOREIGN KEY Constraint[^]

Oracle/PLSQL: Foreign Keys[^]

Create table with foreign key[^]

Regards,
GVPrabu

Posted 22-Feb-13 17:33pm

gvprabu

Comments

chidoskychidosky 23-Feb-13 16:47pm

@GVPrabu, the solution you gave to me is based on the relational database construct but I need the solution in the Object-relational construct as there is much difference between the two. Thanks very much.

Member 10024564 · Answer 2 · 2013-05-16T23:27:00

hai friend,

SQL

create table emp
(emp_id VARCHAR2(5),
title VARCHAR(15),
firstname VARCHAR(20),
surname VARCHAR(20),
dept_id number,
CONSTRAINT emp_dept_fk FOREIGN KEY (dept_id)
REFERENCES dept(dept_id))


create table dept
(dept_id number primary key,
dept_name varchar2(50))

syntax

SQL

CONSTRAINT CONSTRAINT_NAME FOREIGN KEY (CHILDTABLE COLUMN)
REFERENCES PARAENTTABLE_NAME(PARAENTTABLE COLUMN))