SQL ALTER TABLE


David Haertzen David Haertzen, Tutorial Author
Check out our
Data Warehousing Tutorial.
Previous | Next

SQL > SQL Administration > SQL ALTER TABLE Statement

What is the SQL ALTER TABLE Statement?

The SQL ALTER TABLE statement is the SQL command that makes changes to the definition of an SQL table.

Why Use the SQL ALTER TABLE Statement?

Anytime you want to change the definition of an SQL table. For example, you could:

  • Add a column to a table
  • Change the definition of an existing column in a table
  • Drop a column from a table

How To Use the SQL ALTER TABLE Statement

The SQL ALTER TABLE command is used as follows.

SQL ALTER TABLE Statement Syntax

 
ALTER TABLE <table_name>
ADD <column_name1> <datatype1> <constraint1>
 
 
ALTER TABLE <table_name>
ALTER COLUMN <column_name1> <datatype1> <constraint1>
 
 
ALTER TABLE <table_name>
DROP COLUMN <column_name1> <datatype1>
 
 

SQL ALTER TABLE Statement Example 1 - Add a Column

The following example adds a new column into the person table. Before the operation takes place the following columns exists in the table:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(20) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

This ALTER TABLE Statement is executed:

 
ALTER TABLE PERSON
ADD marital_status_code CHAR(1) NULL
 

Results from the execution of the SQL ALTER TABLE statement are:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(20) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL
marital_status_codeCHAR(1) NULL

SQL ALTER TABLE Statement Example 2 - Alter a Column Datatype

The following example changes the datatype of an existing column in the person table. Before the operation takes place the following columns exists in the table:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(20) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

This ALTER TABLE Statement is executed:

 
ALTER TABLE PERSON
ALTER COLUMN person_name VARCHAR(50) NOT NULL
 

Results from the execution of the SQL ALTER TABLE statement are:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(50)NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL
marital_status_codeCHAR(1) NULL

SQL ALTER TABLE Statement Example 3 - Drop a column

The following example removes an existing column in the person table. Before the operation takes place the following columns exists in the table:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(50) NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
apartment_nbr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

This ALTER TABLE Statement is executed:

 
ALTER TABLE dbo.PERSON
DROP COLUMN apartment_nbr
 

Results from the execution of the SQL ALTER TABLE statement are:

Column NameDatatype Nullability
person_id INT NOT NULL
person_name VARCHAR(50)NOT NULL
gender_code CHAR(1) NULL
line_1_addr VARCHAR(50) NULL
line_2_addr VARCHAR(50) NULL
city_name VARCHAR(50) NOT NULL
state_code CHAR(2) NOT NULL
zip_code CHAR(9) NOT NULL

Copyright© 1999-2015, First Place Software, Inc.