ALTER TABLE

After you’ve spent time designing the perfect table, someone is going to come along and ask you a question that will lead you to changing your table. It can be something as simple as, hey, we can only store 25 characters for a city name, we need to have 50. Or it can be a little more complex, like adding or dropping a column. It can also be the worst case scenario, where you’re asked to re-order the columns.

By the way, that requires you backing up the table to a separate table, dropping the original table, creating a new table with the updated order, then select all the data back into that table. And don’t forget to drop your backup table when you’re finished!

Making changes to tables is pretty easy, you just need the ALTER TABLE command. The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

SQL ALTER TABLE Syntax

To add a column in a table, use the following syntax:

ALTER TABLE tableName
ADD column_name datatype

To delete a column in a table, use the following syntax (notice that some database systems don’t allow deleting a column):

ALTER TABLE table_name
DROP COLUMN columnName

To change the data type of a column in a table, use the following syntax:

ALTER TABLE tableName
ALTER COLUMN columnName datatype

SQL ALTER TABLE Example

Look at the “Persons” table:

P_IdLastNameFirstNameAddressCity
1SkywalkerLuke1 Dry-Ass Desert WayGreater Mos Eisley
2LarsOwen1 Dry-Ass Desert WayGreater Mos Eisley
3AckbarAdmiral223 Great Mon Calamari DriveMon Calamari City

Now we want to add a column named “DateOfBirth” in the “Persons” table.

We use the following SQL statement:

ALTER TABLE Persons
ADD DateOfBirth date

Notice that the new column, “DateOfBirth”, is of type date and is going to hold a date. The data type specifies what type of data the column can hold.

The “Persons” table will now like this:

P_IdLastNameFirstNameAddressCityDateOfBirth
1SkywalkerLuke1 Dry-Ass Desert WayGreater Mos Eisley
2LarsOwen1 Dry-Ass Desert WayGreater Mos Eisley
3AckbarAdmiral223 Great Mon Calamari DriveMon Calamari City

Change Data Type Example

Now we want to change the data type of the column named “DateOfBirth” in the “Persons” table.

We use the following SQL statement:

ALTER TABLE Persons
ALTER COLUMN DateOfBirth year

Notice that the “DateOfBirth” column is now of type year and is going to hold a year in a two-digit or four-digit format.

DROP COLUMN Example

Next, we want to delete the column named “DateOfBirth” in the “Persons” table.

We use the following SQL statement:

ALTER TABLE Persons
DROP COLUMN DateOfBirth

The “Persons” table will now like this:

P_IdLastNameFirstNameAddressCity
1SkywalkerLuke1 Dry-Ass Desert WayGreater Mos Eisley
2LarsOwen1 Dry-Ass Desert WayGreater Mos Eisley
3AckbarAdmiral223 Great Mon Calamari DriveMon Calamari City

If you have any questions, send them in! I’m here to help!

Tags: , , , , ,

Trackbacks/Pingbacks

  1. Microsoft Exam 70-433 | Shannon Lowder - 20110412

    […] and alter tables. This objective may include but is not limited to: computed and persisted columns; schemas; scripts […]

  2. SQL201-The Syllabus | Shannon Lowder - 20110607

    […] ALTER TABLE – trust me, requirements change…therefore tables change […]

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.