Renaming a Table- Tables and Constraints

There are a couple of reasons for renaming a table:
• Make the table conform to standards
• Better determine whether the table is being used before you drop it

This example renames a table, from INVENTORY to INV:

SQL> rename inventory to inv;

Adding a Column

Use the ALTER TABLE … ADD statement to add a column to a table. This example adds a column to the INV table:

SQL> alter table inv add(inv_count number);

Altering a Column

Occasionally, you need to alter a column to adjust its size or change its data type. Use the ALTER TABLE … MODIFY statement to adjust the size of a column.

SQL> alter table inv modify inv_desc varchar2(256);

Making a column a larger size is easier, but if you want to decrease the size, you need to verify that there are no values in the column that are greater:

SQL> select max(length(inv_desc)) from inv;

When you change a column to NOT NULL, there must be a valid value for each column. First, verify that there are no null values:

SQL> select inv_count from inv where inv_count is null;

You can alter the table to have a default value, which will help with any new values and inserts, but it just a quick UPDATE statement to change to a value, and then you can run the alter table statement:

SQL> alter table inv modify (inv_count not null); SQL> alter table inv modify (inv_count default 0);

If you want to remove the default value of a column, then set it to NULL like so:SQL> alter table inv modify (inv_count default NULL);.
Sometimes, you need to change a table’s data type; for example, a column that was originally incorrectly defined as a VARCHAR2 needs to be changed to a NUMBER. Before you change a column’s data type, first verify that all values for an existing column are valid numeric values. There is an Oracle function for this, VALIDATE_CONVERSION. You can use the VALIDATE_CONVERSION:

SQL> select validate_conversion(‘1000’ as number);
SQL> select validate_conversion(‘June 24, 2023, 20:34’ as date ‘Month dd, YYYY, HH24MI’)

Or you can use ON CONVERSION ERROR syntax:

SQL> select to_number(‘1000’ default null on conversion error);