Database Tutorial

You can use the ALTER statement to modify the structure of objects, like tables. These examples demonstrate how to add, drop, rename, and modify table elements.

ALTER TABLE table_name ADD Column Statement

The following shows you how to add a new column to an existing table. You can also add two or more columns. This one adds a single column at the end of the list of columns:

1
2

ALTER TABLE military_user
  ADD (batch_process NUMBER);

Unlike MySQL, you can’t include the COLUMN keyword because its not optional and disallowed in this context. This one adds a two columns at the end of the list of columns:

1
2
3

ALTER TABLE military_user
  ADD (batch_process NUMBER)
  ADD (batch_processed_date DATE);

There is no syntax to reorganize the order of columns. You must drop and recreate the table in an Oracle database to reorder the columns.

ALTER TABLE table_name ADD CONSTRAINT Statement

You can add a unique constraint on a column or set of columns after you create the table. The syntax to add a unique constraint puts the column name or a comma-separated list of column names inside parentheses like:

1
2

ALTER TABLE military_user
  ADD CONSTRAINT uu_military_user_1 UNIQUE (military_user, military_branch, military_rank);

The following shows you how to add one FOREIGN KEY constraint at a time after you’ve already created the table. This is very useful when the table you need to define a foreign key that references another table that must be created after your table because it can’t be done when you create your table. The CONSTRAINT constraint_name is an optional phrase in the statement. It is a best practice to provide meaningful constraint names because they show up in error messages when DML statements attempt to violate the constraint. When you leave the phrase out of the statement, the system generated constraint name is displayed. System generated constraint names can be researched in the database catalog but it slows problem resolution time.

1
2
3
4
5

ALTER TABLE military_user
  ADD CONSTRAINT fk_military_user_1 FOREIGN KEY (created_by) REFERENCES military_user (military_user_id);
 
ALTER TABLE military_user
  ADD CONSTRAINT fk_military_user_2 FOREIGN KEY (last_updated_by) REFERENCES military_user (military_user_id);

The following shows you how to DROP a set of constraints, which means you don’t have to write two ALTER statements.

1
2
3

ALTER TABLE military_user
  DROP CONSTRAINT fk_military_user_1
  DROP CONSTRAINT fk_military_user_2;

If these constraints where dropped, you can add back both of them with different constraint names in a single statement, like this:

1
2
3

ALTER TABLE military_user
  ADD CONSTRAINT fk1_military_user FOREIGN KEY (military_user_branch) REFERENCES military_lookup (military_lookup_id)
  ADD CONSTRAINT fk2_military_user FOREIGN KEY (military_user_rank) REFERENCES military_lookup (military_lookup_id);

ALTER TABLE table_name DROP Column Statement

The following shows you how to drop an existing column from a table. You can also drop two or more columns with a single ALTER command.

1
2

ALTER TABLE military_user
  DROP COLUMN batch_process;

ALTER TABLE table_name DROP CONSTRAINT Statement

The following shows you how to DROP a set of constraints, which means you don’t have to write two ALTER statements to do that. You could write two statements if you prefer, but why take the time.

1
2
3

ALTER TABLE military_user
  DROP CONSTRAINT fk_military_user_1
  DROP CONSTRAINT fk_military_user_2;

ALTER TABLE table_name MODIFY Column Statement

The following shows you how to change the data type of a column in an existing table. This one changes a single column’s data type to an INTEGER data type:

1
2

ALTER TABLE military_user
  MODIFY (batch_process INTEGER);

There’s a huge caveat with changing the data type of an existing column’s data type. You can’t change the data type when there’s data in the table, unless the database knows how to implicitly convert the existing data to the new data type.

ALTER TABLE table_name RENAME TABLE Statement

The following shows you how to RENAME a table by using the ALTER statement.

ALTER TABLE military_user RENAME TO military_system_user;

ALTER TABLE table_name RENAME COLUMN Statement

The following shows you how to RENAME a column within a table by using the ALTER statement.

ALTER TABLE military_user RENAME COLUMN middle_initial TO middle_name;