SQL - ALTER Statement

CIW Course in a Nutshell

SQL, ALTER Statement

There will be times when you need to make changes to a database tables' structure, or schema. To do this you may use the ALTER statement.

ALTER TABLE `myciw`.`yetanothertable`
CHANGE COLUMN `LastName` `Surname` VARCHAR(20),
ADD COLUMN `NewColumn` INTEGER UNSIGNED NOT NULL AFTER `SurName`;

The above example is the MySQL code to alter a table. We have renamed column LastName to Surname and then added NewColumn after the newly renamed column SurName.

CHANGE COLUMN does not work in MS SQL, a column can only be renamed by using the stored procedure sp_rename

We used ADD COLUMN to create a new field in the table, equally we can use DROP COLUMN to remove a field.

DROP COLUMN FirstName

would remove the column FirstName, deleting all data that it contains

See the MySQL manual entry for ALTER TABLE for the full syntax.

There is also an ALTER DATABASE command to make changes to the underlying database.

However, there is no equivalent ALTER INDEX command, you must use DROP INDEX and CREATE INDEX to delete and recreate a new index to make changes.