ALTER Statement
This page shows you how to use the ALTER
statement. The ALTER
statement is a DDL statement. It lets you modify the structure of tables, sequence, views, object, and such based on the implementation of the database. It also lets you rename columns of a table.
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); |
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); |
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); |
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); |
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; |
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 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 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 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); |
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 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; |
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; |
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; |
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; |
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); |
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); |
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); |
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); |
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; |
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 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; |
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 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; |
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 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; |
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
Written by michaelmclaughlin
September 22nd, 2009 at 11:31 pm
Posted in
6 Responses to 'ALTER Statement'
Subscribe to comments with RSS or TrackBack to 'ALTER Statement'.
On the ‘ALTER TABLE table_name DROP Column Statement’ code, when the column data type is included (NUMBER in this case), sqlplus returns an error: ‘ORA-00933: SQL command not properly ended’
When the data type is omitted, the table is altered successfully.
Banyan
8 Mar 10 at 12:16 pm
Another great catch on the editing. 😉 It’s also fixed.
michaelmclaughlin
8 Mar 10 at 1:42 pm
It wasn’t clear to me that the MySQL MODIFY COLUMN and CHANGE COLUMN statements would remove an existing NOT NULL constraint from the specified column.
Vaughn T.
20 Oct 13 at 10:00 pm
Vaughn, Modified with hopes of greater clarity.
michaelmclaughlin
22 Oct 14 at 2:44 am
All of the examples on this page that require horizontal scrolling won’t scroll. I have tried FireFox, Chrome and Safari without any change.
Chase
25 Feb 17 at 4:32 am
Chase, Great catch. It’s a limitation of my horizontal scrolling implementation. It’s something that’ll I’ll look at in the future.
michaelmclaughlin
23 Dec 17 at 3:12 pm