6 Replies Latest reply: Feb 22, 2013 5:17 AM by Nitesh. RSS

    Setting primary key for Object creation

    930901
      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
          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.
            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
              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.
                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
                  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.
                    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..