Database Reference
In-Depth Information
18.4
Changing Table Structure
As a designer, you will discover that there are many reasons why the struc-
ture of a table must be changed after it is initially designed and created. For
example, a table storing information on credit applications may contain a
column that is no longer used because the application form has been
changed. Occasionally, the datatype of a column was incorrectly specified in
the original design or the column's name was misspelled.
Many attributes and properties of tables can be changed. We focus on
these types of changes:
Table Changes
. The name of a table can be changed and a table can
be moved. Moving a table is a database administration task because it
is commonly used to relocate tables between different tablespaces.
Adding or Changing Columns
. A column's datatype, length, or
name can be changed. You can change a column from nullable to not
nullable as well.
Removing Columns
. Removing a column from a table can remove
both or either of the column or its data from existing rows.
Rebuilding Tables
. An Oracle-supplied package in Oracle Database
9
i
allowed changes that previously required a great deal of effort.
Changes such as reordering the columns in a table, adding columns
between existing columns, and changing the primary key of a table
can be done with this package.
Figure 18.18 is a general syntax diagram for the ALTER TABLE state-
ment. Altering of constraints is covered in Chapter 20. Looking at the com-
plexity of the syntax diagram in Figure 18.18 should tell you why the
syntax is broken into small pieces for the CREATE TABLE command. In
my mind, too much complexity at once simply creates confusion, so it's eas-
ier to break things into smaller, more manageable pieces.
Note:
Constraints are covered in Chapter 20.
