Chamal Chamal - 2 months ago 8
SQL Question

PL/SQL With Object Types PRIMARY KEY

I have create object relation type as below.

CREATE OR REPLACE TYPE familycar_t AS OBJECT (
make VARCHAR (10),
model VARCHAR(10),
year NUMBER (4),
fuel_type VARCHAR (10));
/

CREATE OR REPLACE TYPE sedan_t AS OBJECT (
familycar ref familycar_t,
cylinder_Size number(10));
/

CREATE TABLE familycar OF familycar_t (PRIMARY KEY (make,model));


Now I want to set primary for "Sedan" table as follow.

CREATE TABLE sedan OF sedan_t (PRIMARY KEY (familycar_t.make,familycar_t.model));


But Error occur like,


Error starting at line : 4 in command - CREATE TABLE sedan OF sedan_t
(PRIMARY KEY (familycar_t.make,familycar_t.model)) Error report - SQL
Error: ORA-00904: "FAMILYCAR_T"."MAKE": invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:

*Action:


What is the solution for it?

Answer

You cannot create an index on attributes whose type is REF (see here). You can only define indexes on REF attributes or columns if the REF is scoped.

CREATE OR REPLACE TYPE sedan_t AS OBJECT (
    familycar familycar_t,
    cylinder_Size  number(10)
    );
/
CREATE TABLE sedan OF sedan_t (PRIMARY KEY (familycar.make, familycar.model));

Table SEDAN created.
Comments