Help adding a NOT NULL column

itsonlyme44 · #1 (**permalink**) 03-31-09, 10:27

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?

ARWinner · #2 (**permalink**) 03-31-09, 10:42

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

nick.ncs · #3 (**permalink**) 03-31-09, 11:08

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

stolze · #4 (**permalink**) 03-31-09, 15:05

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech