Saturday, April 12, 2014

adding NOT NULL columns to an existing table ... implications make me grumpy

This is DBA basics 101 in the oracle world but well also something that we grumpy DBA types forget from time to time.  We have an existing table in a schema that is populated with data.  Something like this say:

create table dbaperf.has_data ( column_one varchar2(10) not null, column_two number(10) not null);

insert into dbaperf.has_data(column_one, column_two) values('First row',13);
insert into dbaperf.has_data(column_one, column_two) values('Another',42); commit;

Now you need to add another column that is also NOT NULL.  Chris Date not happy the vendor implementations of the relational model allow null columns.  Be aware of any potential NULL columns in rows and handle them carefully ( IS null / IS not null ) to avoid messing up results.

But anyhow we are going to add in a new column that is NOT NULL.

How easy that is to do against an Oracle table depends on whether one is also supplying a DEFAULT value for the new column.  If you do not supply DEFAULT value what happens here?

 alter table dbaperf.has_data add ( column_three char(1) NOT NULL );

You get: ORA-01758: table must be empty to add mandatory (NOT NULL) column

To get around that you have to do this in three steps:
  • Add in the new column
  • Populate all the new columns with a value ( data migration )
  • Make the column NOT NULL
alter table dbaperf.has_data add ( column_three char(1) );

update dbaperf.has_data set column_three = 'X' where column_one = 'First row';
update dbaperf.has_data set column_three = 'X' where column_one = 'Another';

alter table dbaperf.has_data modify ( column_three NOT NULL );

Things get easier if you do this with a DEFAULT clause on the new column.  The problem is of course some columns have a reasonable default value others may not get any agreement for a default value.  A min and a max type column probably can have an easy default others not so much.

alter table dbaperf.has_data add ( column_four number(21,2) default 0 NOT NULL );

All of this discussion side steps the implications of adding a new column to a large existing table or partitioned table and fragging up the blocks ... that is a little beyond 101 for now.

No comments:

Post a Comment