Database Tutorial

Oracle

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;

MySQL

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 int unsigned;

You can accomplish this without specifying a COLUMN keyword because it is the default data type. In MySQL, the COLUMN keyword is optional. This means that the following statement is exactly the same as the preceding statement:

1
2

ALTER TABLE military_user
  ADD COLUMN batch_process int unsigned;

Multiple columns can be added much as they are in Oracle. The only difference is you need a comma between each ADD subclause, like this:

1
2
3

ALTER TABLE military_user
  ADD COLUMN batch_process int unsigned
, ADD COLUMN batch_processed_date date;

After you ADD the column(s), you can use the CHANGE subclause to reorganize their order.

ALTER TABLE table_name ADD CONSTRAINT Statement

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 fk1_military_user FOREIGN KEY (military_user_branch) REFERENCES military_lookup (military_lookup_id);
 
ALTER TABLE military_user
  ADD CONSTRAINT fk2_military_user FOREIGN KEY (military_user_rank) REFERENCES military_lookup (military_lookup_id);

Writing two ALTER TABLE statements to add multiple constraints works but you can do it with less effort by writing a single command. The syntax is very similar to the ALTER TABLE statement when you’re dropping FOREIGN KEY constraints. An example of such a DDL ALTER statement follows:

1
2
3

ALTER TABLE military_user
  ADD CONSTRAINT fk3_military_user FOREIGN KEY (created_by) REFERENCES military_user (military_user_id)
, ADD CONSTRAINT fk4_military_user FOREIGN KEY (last_updated_by) REFERENCES military_user (military_user_id);

You should note that the MySQL syntax requires a comma between the two ADD clauses, which is not the case for an Oracle database.

You can also add an INDEX to an existing table with the following ADD CONSTRAINT syntax variation:

1

ALTER TABLE military_lookup ADD CONSTRAINT un1_military_lookup UNIQUE INDEX (table_name, column_name, lookup_type, lookup_value, lookup_code);

What you can’t do here is conveniently add a NOT NULL constraint to a column. You actually use the ALTER TABLE table_name MODIFY, as seen later in this page.

ALTER TABLE table_name CHANGE COLUMN Statement

The ability to rename a column is intrinsically tied to the same command structure that allows you to change the data type of a column in MySQL. You rename a column with the following syntax but you don’t change the data type or it’s precision (database lingo for size):

ALTER TABLE military_user CHANGE COLUMN middle_initial middle_name VARCHAR(20);

You can also add a NOT NULL constraint with the ALTER TABLE table_name CHANGE statement. As you can see, this syntax requires typing the column name twice, which can be avoided if you use the ALTER TABLE table_name MODIFY statement.

ALTER TABLE military_user CHANGE COLUMN batch_process batch_process INT UNSIGNED NOT NULL;

The foregoing also shows how to change the data type because the original one was a VARCHAR(1), which is generally too short for a middle name. Another opportunity exists with this command that lets you re-order column names in MySQL. The following statement re-orders column names while changing the middle_initial column name to middle_name.

ALTER TABLE military_user
  CHANGE first_name  first_name  VARCHAR(20) AFTER military_user_rank
, CHANGE last_name   last_name   VARCHAR(20) AFTER first_name
, CHANGE middle_name middle_name VARCHAR(20) AFTER last_name;

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. MySQL requires that you put the DROP clauses into a comma delimited list.

1
2

ALTER TABLE military_user
  DROP batch_process;

The COLUMN keyword is optional but you may include it for clarity. This shows the full phrase with the optional COLUMN keyword.

1
2

ALTER TABLE military_user
  DROP COLUMN batch_process;

ALTER TABLE table_name DROP FOREIGN KEY Statement

The following shows you how to DROP the two constraints the previous statement added to the MILITARY_USER table. It’s a convenient shorthand that lets a single statement perform multiple activities. Naturally, you could break this up into two statements but that begs the question of why you would want to break up into two statements. You should note that its dropping the FOREIGN KEY structures, not a CONSTRAINT like an Oracle database.

1
2
3

ALTER TABLE military_user
  DROP FOREIGN KEY fk_military_user_1
, DROP FOREIGN KEY fk_military_user_2;

Unlike the Oracle syntax, which simply would list multiple DROP subclauses, this syntax requires a comma between multiple DROP subclauses. The preceding query lets us locate the execution of the ALTER TABLE by providing a query returning a single string literal value.

ALTER TABLE table_name DROP INDEX Statement

The following shows you how to DROP a unique index from the MILITARY_LOOKUP table. You should note that the statement drops the INDEX not the CONSTRAINT.

1

ALTER TABLE military_lookup DROP INDEX un1_military_lookup;

ALTER TABLE table_name MODIFY COLUMN Statement

This command lets you change the data type, and add or remove a NOT NULL constraint from any column. The COLUMN is an optional key word in this statement. You can also add a NOT NULL constraint with the ALTER TABLE table_name CHANGE statement.

ALTER TABLE military_user MODIFY COLUMN batch_process INT UNSIGNED NOT NULL;

Unlike an Oracle database where you create an INDEX as a separate structure, MySQL creates an INDEX as part of a table. That means when you want to dump an INDEX, you must use an ALTER statement.

SQL Server