3 Replies Latest reply on Jul 14, 2009 1:22 PM by MaximDemenko

    ORA-00902: invalid datatype

    675433
      There is a table t_emp, having column clm1, clm2, clm3, clm4, etc.
      Datatype of columns:-
      clm1 -- Number
      clm2 -- Number(12)
      clm3 -- Char(4)

      Now, I am trying to create a primary key using statement:
      alter table t_emp
      add constraint primary key (clm1, clm2, clm3)
      It gives oralce error ORA-00902: invalid datatype.

      what is wrong at this place?
      Any thought?

      Regards,
      Ritesh
        • 1. Re: ORA-00902: invalid datatype
          21205
          you forgot the constraint name
          alter table t
          add constraint tpk primary key (clm1, clm2, clm3)
          • 2. Re: ORA-00902: invalid datatype
            APC
            You need to give your primary key a name (which is good practice anyway):
            SQL> r
              1  create table t_emp (
              2  clm1  Number,
              3  clm2  Number(12),
              4  clm3  Char(4)
              5* )
            
            Table created.
            
            SQL> alter table t_emp
              2  add constraint primary key (clm1, clm2, clm3)
              3  /
            add constraint primary key (clm1, clm2, clm3)
                                       *
            ERROR at line 2:
            ORA-00902: invalid datatype
            
            
            SQL> ed
            Wrote file afiedt.buf
            
              1  alter table t_emp
              2* add constraint temp_pk primary key (clm1, clm2, clm3)
            SQL> r
              1  alter table t_emp
              2* add constraint temp_pk primary key (clm1, clm2, clm3)
            
            Table altered.
            
            SQL>
            Cheers, APC

            blog: http://radiofreetooting.blogspot.com
            • 3. Re: ORA-00902: invalid datatype
              MaximDemenko
              The error is due to wrong syntax. You can either
              alter table foo add primary key(col1,col2..)
              or
              alter table bar add constraint constraint_name primary key(col1,col2...)
              By the first option you get the constraint name system generated, if you use add constraint - you must give it a name.

              Best regards

              Maxim