1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

facing ORA-01407 error during altering column size

Discussion in 'SQL PL/SQL' started by laxman, Jan 22, 2010.

  1. laxman

    laxman Forum Expert

    Messages:
    144
    Likes Received:
    0
    Trophy Points:
    230
    Location:
    MUMBAI
    Hi all,
    i am facing one problem while modifying the size of one column.

    Here is the details:

    I have one table called as tabA with following attributes:


    SQL> desc tabA;
    Name Null? Type
    ----------------------------------------- -------- ----------------------------
    PID NOT NULL NUMBER(20)
    TO NOT NULL NUMBER(10)
    SDATE NOT NULL DATE
    SCODE NOT NULL NUMBER(10)
    STIME NOT NULL DATE
    ENDTIME DATE
    DURINSECONDS NUMBER(10)

    SQL> select count(*) from tabA;

    COUNT(*)
    ----------
    179691


    Now i need to modify the size of column pid from number(20) to number(10);

    so i am following this steps

    sql> alter table tabA add (PID_bk number(10));

    sql> table altered;

    sql> update tabA set PID_bk = PID;

    sql> commit;

    sql> table altered;

    sql> alter table tabA disable constraint PK_tabA;

    sql> table altered;

    sql> update tabA set PID=null; --- when executing this query i am facing one error mentioned below.


    update tabA set PID=null
    *
    ERROR at line 1:
    ORA-01407: cannot update ("oralax"."tabA"."PID") to NULL

    Elapsed: 00:00:01.08
    ----------------------------------------------------------------------------------

    the below steps are supposed to be executed after the above update statement
    sql> alter table tabA modify (PID number(10));

    sql> update tabA set PID = PID_bk;
    commit;

    sql> alter table tabA enable constraint PK_tabA;

    sql> alter table tabA drop (PID_bk);

    Thanks n regards
    Laxman
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Even though creating a primary key constraint does set the affected columns to NOT NULL disabling a primary key constraint does not make those columns nullable. You need to alter the column to NULL.