This discussion is archived
6 Replies Latest reply: Feb 22, 2013 3:17 AM by Nitesh. RSS

Setting primary key for Object creation

930901 Newbie
Currently Being Moderated
Hi,

My object is,
create or replace type Samplerow as Object
(id number(1) ,
name varchar2(15),
code number(4),
level number(1),
status varchar2(25),
);

Question: When i set primary key for id column in this object, giving errors and warnings.
Can't we set it....?


Regards.
  • 1. Re: Setting primary key for Object creation
    Paul Horth Expert
    Currently Being Moderated
    AshokBottu wrote:
    Hi,

    My object is,
    create or replace type Samplerow as Object
    (id number(1) ,
    name varchar2(15),
    code number(4),
    level number(1),
    status varchar2(25),
    );

    Question: When i set primary key for id column in this object, giving errors and warnings.
    Can't we set it....?


    Regards.
    How can we help if you don't show us what you've tried and what the error was?

    Please read {message:id=9360002} and follow the advice there.

    Edited by: Paul Horth on Feb 22, 2013 9:11 AM

    Edited by: Paul Horth on Feb 22, 2013 9:12 AM
  • 2. Re: Setting primary key for Object creation
    Nitesh. Explorer
    Currently Being Moderated
    Its very clear that type object dont store any data's in a database, so you cant add any constraint to the variables declared in a type object because constraint is not required itself ..

    Rgds,
    Niteshkhush..
  • 3. Re: Setting primary key for Object creation
    odie_63 Guru
    Currently Being Moderated
    Hi,
    AshokBottu wrote:
    Question: When i set primary key for id column in this object, giving errors and warnings.
    Can't we set it....?
    What errors, what warnings on what statement?

    Creating a primary key on one of the object attribute doesn't make sense.

    However, you can add a primary key (and other constraints) to an object table that stores instances of this object.
  • 4. Re: Setting primary key for Object creation
    Nitesh. Explorer
    Currently Being Moderated
    Hey Odie how you are saying as we can add any constraints to objects .. Its we can add to the object which is declared inside table but we cannot add constraint directly to independent type objects know .. I mean if its possible means how so , Please let me know so that i can update my knowledge also .. Its i even tried also but it thrown errors as object itself wont get create because type object is like array which stores different set of datatype's variables but dont store any values know .. Let me know as how possible ??

    Thanks,
    Rgds,
    Nitkhush.
  • 5. Re: Setting primary key for Object creation
    odie_63 Guru
    Currently Being Moderated
    I'll say again, we can add constraints to tables that store instances of the object.

    Maybe an example will be more clear :
    SQL> create or replace type myrow as object (
      2    rid   integer
      3  , rname varchar2(15)
      4  ) ;
      5  /
    
    Type created.
    
    SQL> create table mytable of myrow (
      2    constraint mytable_pk primary key (rid)
      3  , rname not null
      4  ) ;
    
    Table created.
    
    SQL> insert into mytable values ( myrow(1, '') );
    insert into mytable values ( myrow(1, '') )
                                          *
    ERROR at line 1:
    ORA-01400: cannot insert NULL into ("DEV"."MYTABLE"."RNAME")
    
    
    SQL> insert into mytable values ( myrow(1, 'ABC') );
    
    1 row created.
    
    SQL> insert into mytable values ( myrow(1, 'XYZ') );
    insert into mytable values ( myrow(1, 'XYZ') )
    *
    ERROR at line 1:
    ORA-00001: unique constraint (DEV.MYTABLE_PK) violated
  • 6. Re: Setting primary key for Object creation
    Nitesh. Explorer
    Currently Being Moderated
    Ok cleared this only i too meant as type as datatype used inside tables can have constraints but type objects alone cannot have any constraints on any variables as they are independent in nature ..

    Thank you,
    Nitkhush..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points