Using the MS-SQL Server ALTER TABLE DROP COLUMN Clause to Remove a Table Column

The SQL-92 standard does not specify a DROP COLUMN clause as part of the ALTER TABLE statement. As a result, some DBMS products require that you unload data from the table, use the DROP TABLE statement to erase the table, execute the CREATE TABLE statement to re-create the table without the column you want to drop, and then reload the data you unloaded before dropping the table. (Given the steps involved, you might be tempted to just ignore the column you want to drop.)

Fortunately, MS-SQL Server provides a DROP COLUMN clause as part of its ALTER TABLE statement. The syntax for dropping a column is:

ALTER TABLE <table name>

DROP COLUMN <column name>

[,<column name>...,<last column name]

Thus, to drop columns from an EMPLOYEE table defined by

CREATE TABLE employee

(employee_id INTEGER,

first_name VARCHAR(20),

last_name VARCHAR(30),

social_security_number CHAR(11),

street_address VARCHAR(35),

health_card_number CHAR(15),

sheriff_card_number CHAR(15),

badge_number IDENTITY(100,100)

PRIMARY KEY (employee_id))

you could use the ALTER TABLE statement

ALTER TABLE employee

DROP COLUMN health_card_number, sheriff_card_number

to drop the HEALTH_CARD_NUMBER and SHERIFF_CARD_NUMBER columns.

MS-SQL Server will prevent you from dropping a column to which a constraint or default value is assigned. Moreover, you cannot drop a column identified as a FOREIGN KEY in another table. For example, if you attempt to drop the EMPLOYEE_ID from the EMPLOYEE table, MS-SQL Server will respond with the error message

Server: Msg 4922, Level 16, State 3, Line 1

ALTER TABLE DROP COLUMN employee_id failed because PRIMARY

KEY CONSTRAINT PK__employee__6E01572D access this column.

and the ALTER TABLE statement will fail.

In order to drop a column with a default value or constraint, you must first drop the constraint using an ALTER TABLE statement in the form:

ALTER TABLE <table name> DROP CONSTRAINT <constraint name>

In the current example, the CREATE TABLE statement did not specify a name for the PRIMARY KEY constraint on the EMPLOYEE_ID column of the EMPLOYEE table. As a result, the DBMS created the unique constraint name PK_employee_6E01572D for the PRIMARY KEY constraint. Thus, in order to remove the PRIMARY KEY constraint from the EMPLOYEE table in the example, use the Transact-SQL statement:

Note The name the DBMS assigns to an unnamed constraint will differ each time you create a constraint, even if you drop and respecify the same constraint. Also, when dropping a constraint with a DBMS-generated name, be sure to note and use double (vs. single) underscores (__) in the constraint name.

If you want to drop a column that is a FOREIGN KEY in another table, you must first use the ALTER TABLE statement to drop the FOREIGN KEY reference in the other table, and then you drop the column in the current table. (You will learn more about using the ALTER TABLE statement to work with FOREIGN KEY constraints in Tip 60.)