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.

DDL - Part 2 - Column Level attributes

COLUMN LEVEL ATTRIBUTES:

Most of the column level attributes are Teradata extensions.

ANSI has very few column level attributes.

ANSI:

NOT NULL	--> Disallows NULL in the column.
DEFAULT user	--> Uses user id as default value.
DEFAULT value	--> Uses default value is input value is not provided.
DEFAULT NULL	--> Uses NULL as the default value.

Teradata:

UPPERCASE	--> Stores the data entered in upper case.
CASESPECIFIC	--> Treats data as case specific for comparisons and sorting. By default in teradata 'A' and 'a' would mean the same and hence they will sort in any sequence. However when we make it case specific the sorting differs.
FORMAT	--> Used to control display format of a field.
TITLE	--> Used to provide default titles.
NAMED/ AS	--> Uses Default column name.
COMPRESS	--> Used to compress NULL's to take no physical space.
COMPRESS NULL	--> Used to compress NULL's to take no physical space.
COMPRESS value	--> Used to compress a particular value and NULL's to take no physical space
WITH DEFAULT	--> Uses System default values.
DEFAULT DATE	--> Uses today's date as default date.
DEFAULT TIME	--> Uses Current Time as default time.

Note that DEFAULT DATE ,DEFAULT TIME and WITH DEFAULT are all not ANSI.

Example:

CREATE SET TABLE EDW_RESTORE_TABLES.TEST3 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     DATABLOCKSIZE = 21504 BYTES, FREESPACE = 30 PERCENT, CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      COLUMN1 CHAR(1) UPPERCASE)

       PRIMARY INDEX ( COLUMN1 );

insert into EDW_RESTORE_TABLES.TEST3 values ('c')

select * from EDW_RESTORE_TABLES.TEST3

Gives 'C'   Capital C