Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2007
    Posts
    288

    Unanswered: Help adding a NOT NULL column

    DB2 V9 on Windows :

    I have a table that is already populated with Data that the Developer wants me to add a coumn to. The colulm is this:

    Code:
    ALTER TABLE KCDWHPRC.TPH_TEST ADD "MXDX_7PAY_TYP_CD" CHAR(1) NOT NULL
    When I try and add the column I get this error:

    Code:
    ALTER TABLE KCDWHPRC.TPH_TEST ADD "MXDX_7PAY_TYP_CD" CHAR(1) NOT NULL
    DB21034E  The command was processed as an SQL statement because it was not a 
    valid Command Line Processor command.  During SQL processing it returned:
    SQL0193N  In an ALTER TABLE statement, the column "MXDX_7PAY_TYP_CD" has been 
    specified as NOT NULL and either the DEFAULT clause was not specified or was 
    specified as DEFAULT NULL.  SQLSTATE=42601
    
    SQL0193N  In an ALTER TABLE statement, the column "MXDX_7PAY_TYP_CD                                                      " has been specified as NOT NULL and either the DEFAULT clause was not specified or was specified as DEFAULT NULL.
    
    Explanation: 
    
    When new columns are added to a table that already exists, a 
    value must be assigned to that new column for all existing rows.  
    By default, the null value is assigned.  However, since the 
    column has been defined as NOT NULL, a default value other than 
    null must be defined.
    It works when I add the column like this:

    Code:
    ALTER TABLE KCDWHPRC.TPH_TEST ADD "MXDX_7PAY_TYP_CD" CHAR(1) NOT NULL WITH DEFAULT
    But they don't want to use 'WITH DEFAULT' in the DDL. Is there any way to add a NOT NULL COLUMN to a table that is already loaded with Data?

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    When you add a new column to an existing table, you have three options. First is to make it not null, in which case you MUST supply a value to insert into the column for every row using the WITH DEFAULT clause. The second is to make the column nullable and let it be put in with null values. The last is to make it nullable and to give it a default value just like the NOT NULL way.

    Andy

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    IT is but logical that when you specify NOT NULL then to maintain consistency for existing rows, some NOT NULL values are required to be present for those rows.... and for which you have to specify the DEFAULT clause....
    IBM Certified Database Associate, DB2 9 for LUW

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    What you could try is to add the column with the DEFAULT clause (there is no way around it as nick and Andy explained), then reorganize the table, then drop the default again.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •