SQL ALTER TABLE

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

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

ALTER TABLE PERSON ADD marital_status_code CHAR(1) NULL

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:

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

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:

ALTER TABLE dbo.PERSON DROP COLUMN apartment_nbr

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