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

The SQL ALTER TABLE command is used as follows.

SQL ALTER TABLE Statement Syntax

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 Name	Datatype	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:

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

Column Name	Datatype	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_code	CHAR(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 Name	Datatype	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:

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

Column Name	Datatype	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_code	CHAR(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 Name	Datatype	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:

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

Column Name	Datatype	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