Alter Table Statement

ALTER TABLE is used in a statement to manipulate the table structure by adding or deleting a column. This operation should be done with care especially when dealing with a database that may be accessed by multiple scripts. Deleting or DROPping is especially touchy because it is more likely that a script already contains lines that will be affected. Also, huge swaths of data could be wiped out with the push of a button.

The syntax to drop a table would look like this:

ALTER TABLE table
DROP COLUMN column;

Note that since the ALTER TABLE statement actually has to do with the columns, that COLUMN must always be mentioned.

The syntax to add a table is very similar:

ALTER TABLE table
ADD column data_type;

It should be noted here that the data type is also included, e.g. INT, VARCHAR, etc. The data type of the column may also be changed. Interestingly, the keyword COLUMN is required for DROP and ALTER, but not for ADD. In this case ALTER COLUMN is used again before the column name:

ALTER COLUMN column data_type;

Example for adding a column to a table called fict_chars:

idnamedescbook
1Jane Porter5 ft 4 in, blue eyesTarzan
2Scarlet O'Hara5 ft 2 in, green eyesGone with the Wind
3Hermione Granger5 ft 6 in, brown eyesHarry Potter Series

To add a column called "author" of the type VARCHAR with a max of 255 characters the following code would suffice:

ALTER TABLE fict_chars
ADD COLUMN author VARCHAR(255);

The result would look like this:

idnamedescbookauthor
1Jane Porter5 ft 4 in, blue eyesTarzannull
2Scarlet O'Hara5 ft 2 in, green eyesGone with the Windnull
3Hermione Granger5 ft 6 in, brown eyesHarry Potter Seriesnull

Of course, this command will not fill in the blank fields.

< AS Keyword Denotes an ALIAS | AND Operator >


Send notes in disappearing ink!

Interesting Pages