Teradata - Adding a NOT NULL column to table using ALTER table.

Altering table to add a NOT NULL column to a table:

We can add a New column to a table using ALTER TABLE syntax.

ALTER TABLE TABLENAME ADD COLUMN1 INTEGER.

However care needs to be taken when we add a new NOT NULL COLUMN to a table that already has rows.

Assume that we have a table EMPLOYEE with some rows in it.

show table EMPLOYEE

CREATE SET TABLE MY_TEST_TABLES.EMPLOYEE ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      Employeeid INTEGER,

      DepartmentNo INTEGER,

      Salary DECIMAL(8,2),

      Hiredate DATE FORMAT 'YYYY-MM-DD')

PRIMARY INDEX ( Employeeid )

INDEX ( DepartmentNo ) ORDER BY VALUES ( DepartmentNo );

If we try to add a new NOT NULLL column called hike we would get as error as shown below:

alter table employee add hike decimal(10,2) not null;

ALTER TABLE Failed. 3559:  Column HIKE is not NULL and it has no default value. 

Normally if we add a new column , the existing rows get NULL of the new column.

However when we make it NOT NULL, there is no value that can be assigned to the new column for existing rows.

The new column must be initially set to Null or a value for existing rows.

By adding the WITH DEFAULT phrase or DEFAULT phrase , the NOT NULL attribute is permitted. The new column being added will carry the system default value initially.

Example :

alter table employee add hike decimal(10,2) not null default 0;

alter table employee add hike decimal(10,2) not null WITH DEFAULT;

Moral of the story is that if we want a new column to be NOT NULL, we need to assign some value to the existing rows.