Modifying Table Schemas

A time may come when you decide that you absolutely must add or remove a column from a table. To do this, you use the ALTER TABLE statement, which has many possible uses. We will demonstrate the more common ones here:

 ALTER TABLE TableName   ADD ColumnName ColumnType attributes...; ALTER TABLE TableName   DROP COLUMN ColumnName; ALTER TABLE TableName   CHANGE COLUMN ColumnName New_Details; ALTER TABLE TableName   RENAME AS NewTableName; 

Altering tables is something that should be done rarely, when you are certain it is the correct course of action. Our goal in designing databases was to create a table structure that was efficient, flexible, and scalable for our future needs. If you find yourself having to change database schemas frequently, it might be a hint that your process for designing tables deserves a review. In addition to these considerations, altering tables can be extremely expensive. Some servers, when dropping columns, require large amounts of disk space and lock the table for the entire time it takes to remove the column data no longer being used. Similarly, adding columns can cause some temporary if not permanent performance problems.

To add a column, you use the ADD clause, and specify, in addition to the new column name, its data type and any attributes you would like for it to have. For example, we might decide to add a password field to our Users table:

 ALTER TABLE Users   ADD password VARCHAR(50) NOT NULL; 

This query would add a new column to this table as a string and would not allow NULL as a value for any row. Existing rows in the table would have their value for this column set to the empty string (''). There are the optional keywordsFIRST and AFTERthat allow you to specify where the new columns are to go:

 ALTER TABLE Users   ADD password VARCHAR(50) NOT NULL   AFTER user_name; 

To delete a column from a table, we simply use the DROP COLUMN clause. For example, to delete the password column we just added

 ALTER TABLE Users   DROP COLUMN password; 

As with other operations that result in the removal of information, dropping a column is a very permanent operation and cannot be undone. Therefore, it should be used with extreme caution (and rarely granted as a permission to database users).

To change the definition of a column, use the CHANGE (also called MODIFY) clause. If you specify a name in the new definition for a column, the column is renamed. Otherwise, you can use this to change the type or attributes on the column. For example, to change the user_name field in our Users table to be a 100-character string instead of 50

 ALTER TABLE Users   CHANGE COLUMN user_name VARCHAR(100) NOT NULL; 

To rename our Users table, we use the RENAME clause:

 ALTER TABLE Users   RENAME AS MessageBoardUsers; 

Only database users connected to the database server with the ALTER privilege can execute the ALTER TABLE statement.