Skip to content

Add NOT NULL Columns Directly

Legacy upgrade scripts suggest a comparatively lengthy procedure in introducing a NOT NULL column to a database table. It appears that this same procedure continues to inherit on the codes written later. However, there exist some short-hand methods to do the same from the current installation framework.

Previous Method of Implementation

  • Insert a nullable column.
  • Populate the column with some value.
  • Make it NOT NULL.

Example:

DECLARE
   table_name_ VARCHAR2(30) := 'PEOPLE_TAB';
   columns_    Database_SYS.ColumnTabType;
BEGIN
   Database_SYS.Set_Table_Column(columns_, 'AGE', 'NUMBER', 'Y');    
   Database_SYS.Alter_Table(table_name_, columns_, TRUE);   
END;
/

UPDATE PEOPLE_TAB t
   SET t.age = 20;

COMMIT;

DECLARE
   table_name_ VARCHAR2(30) := 'PEOPLE_TAB';
   columns_    Database_SYS.ColumnTabType;
BEGIN
   Database_SYS.Set_Table_Column(columns_, 'AGE', nullable_ => 'N');    
   Database_SYS.Alter_Table(table_name_, columns_, TRUE);   
END;
/

This will result in a final resultant column as follows:
image-20210728163840250

Suboptimal Attempt

Alternatively, we can add a NOT NULL column with a default value as follows:
Note: This method is not recommended.

DECLARE
   table_name_ VARCHAR2(30) := 'PEOPLE_TAB';
   columns_    Database_SYS.ColumnTabType;
BEGIN
   Database_SYS.Set_Table_Column(columns_, 'AGE', 'NUMBER', 'N', '20');    
   Database_SYS.Alter_Table(table_name_, columns_, TRUE);   
END;
/

Added column will look something like this:
image-20210728164822751

Note that a default value has been added unintentionally. This will lead to differences in data structures in fresh installations and upgrades.

Refined Solution

To prevent the default value being added, take advantage of the ‘keep_default’ parameter. By setting this to ‘N’ to denote NO, will suppress the addition of default value onto the column.

DECLARE
   table_name_ VARCHAR2(30) := 'PEOPLE_TAB';
   columns_    Database_SYS.ColumnTabType;
BEGIN
   Database_SYS.Set_Table_Column(columns_, 'AGE', 'NUMBER', 'N', '20', keep_default_ => 'N');
   Database_SYS.Alter_Table(table_name_, columns_, TRUE);   
END;
/

Note the use of keep_default_ => 'N' in the code.

Resultant column is as follows:
image-20210728170538954