Home » SQL & PL/SQL » SQL & PL/SQL » Primary keys and OIDs
Primary keys and OIDs [message #219291] Tue, 13 February 2007 20:19 Go to next message
sophia_786
Messages: 13
Registered: February 2007
Location: manchester
Junior Member


Hi, everyone!

here is the problem...im trying to create a project table, which links to certain activities. As a single activity can be linked to a number of projects i created a like type (ProjectActivity).

Each single activity can have several tasks. However a single task can also be linked back to a number of activities.

so i created another link (ActivityTask).



Here is my SQL:

/* Details */

CREATE OR REPLACE TYPE Details_objtyp AS OBJECT (
DESCRIPTION VARCHAR2(200),
IDENTIFIER VARCHAR2(100),
EST_HOURS NUMBER,
EST_COMPLETE DATE,
IS_ACTIVE VARCHAR2(3),
CREATED DATE,
UPDATED VARCHAR2(200),
CREATED_BY VARCHAR2(200),
UPDATED_BY VARCHAR2(200)
);
/

/* Project */

CREATE OR REPLACE TYPE Project_objtyp AS OBJECT (
PRJ_ID VARCHAR2(70)
);
/

/* Activity */

CREATE OR REPLACE TYPE Activity_objtyp AS OBJECT (
ACT_ID VARCHAR2(70)
);
/

/* Task */

CREATE OR REPLACE TYPE Task_objtyp AS OBJECT (
TSK_ID VARCHAR2(70)
);
/

CREATE OR REPLACE TYPE ActivityTask_objtyp AS OBJECT (
ACT_ID REF Activity_objtyp,
TSK_ID REF Task_objtyp
);
/

/* ProjectAvtivity */

CREATE OR REPLACE TYPE ProjectActivity_objtyp AS OBJECT (
PRJ_ID REF Project_objtyp,
ACT_ID REF ActivityTask_objtyp
);
/


CREATE OR REPLACE TYPE TimesheetItems_objtyp AS OBJECT (
TSI_TIMESHEET_ITEMS_ID VARCHAR2(9),
TSI_DATE_ITEM DATE,
TSI_HOURS_TOTAL VARCHAR2(9),
TSI_DATE_REC_CREATED DATE,
TSI_DATE_REC_UPDATED DATE,
TSI_UPDATED_BY VARCHAR2(60),
PRJ_ID REF ProjectActivity_objtyp
);
/

/* Project table */

CREATE TABLE Project_objtab OF Project_objtyp (
PRIMARY KEY (PRJ_ID))
OBJECT IDENTIFIER IS PRIMARY KEY;

/* Activity table */

CREATE TABLE Activity_objtab OF Activity_objtyp (
PRIMARY KEY (ACT_ID))
OBJECT IDENTIFIER IS PRIMARY KEY;

/* Task table */

CREATE TABLE Task_objtab OF Task_objtyp (
PRIMARY KEY (TSK_ID))
OBJECT IDENTIFIER IS PRIMARY KEY;

/* ActivityTask table */

CREATE TABLE ActivityTask_objtab OF ActivityTask_objtyp (
FOREIGN KEY (ACT_ID) REFERENCES Activity_objtab,
FOREIGN KEY (TSK_ID) REFERENCES Task_objtab)
OBJECT IDENTIFIER IS PRIMARY KEY;


/* ProjectActivity table */

CREATE TABLE ProjectActivity_objtab OF ProjectActivity_objtyp (
FOREIGN KEY (PRJ_ID) REFERENCES Project_objtab,
FOREIGN KEY (ACT_ID) REFERENCES ActivityTask_objtyp)
OBJECT IDENTIFIER IS PRIMARY KEY;

when I create table for ActivityTask_objtab or ProjectActivity_objtab, the error message is:ORA-22971: invalid datatype for PRIMARY KEY-based object identifier. How can I fix this problem? thanks!
Re: Primary keys and OIDs [message #219294 is a reply to message #219291] Tue, 13 February 2007 20:30 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't know how to build object tables, so I looked at the manual.

CREATE TABLE ProjectActivity_objtab OF ProjectActivity_objtyp (
PRIMARY KEY (PRJ_ID, ACT_ID),
FOREIGN KEY (PRJ_ID) REFERENCES Project_objtab,
FOREIGN KEY (ACT_ID) REFERENCES ActivityTask_objtyp)
OBJECT IDENTIFIER IS SYSTEM GENERATED;


Ross Leishman
Re: Primary keys and OIDs [message #219296 is a reply to message #219294] Tue, 13 February 2007 20:54 Go to previous messageGo to next message
sophia_786
Messages: 13
Registered: February 2007
Location: manchester
Junior Member

Hi,

i tried to run that code, but still hitting an error...

this time...


ORA-02444: Cannot resolve referenced object in referential constraints

Sad

[Updated on: Tue, 13 February 2007 20:54]

Report message to a moderator

Re: Primary keys and OIDs [message #219331 is a reply to message #219291] Wed, 14 February 2007 01:00 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
With all due respect - why do you need OBJECTs at all?
Re: Primary keys and OIDs [message #219377 is a reply to message #219331] Wed, 14 February 2007 03:35 Go to previous message
sophia_786
Messages: 13
Registered: February 2007
Location: manchester
Junior Member

its an assignment from university, i have to use objects im afraid!

Previous Topic: Need Help in Grouping
Next Topic: Updation Problem
Goto Forum:
  


Current Time: Sat Dec 03 12:24:56 CST 2016

Total time taken to generate the page: 0.09569 seconds