Dropping Columns

Go Up to Before using ALTER TABLE

Before attempting to drop or modify a column, you should be aware of the different ways that ALTER TABLE can fail:

• The person attempting to alter data does not have the required privileges.
• Current data in a table violates a PRIMARY KEY or UNIQUE constraint definition added to the table; there is duplicate data in columns that you are trying to define as PRIMARY KEY or UNIQUE.
• The column to be dropped is part of a UNIQUE, PRIMARY, or FOREIGN KEY constraint.
• The column is used in a CHECK constraint. When altering a column based on a domain, you can supply an additional CHECK constraint for the column. Changes to tables that contain CHECK constraints with sub-queries can cause constraint violations.
• The column is used in another view, trigger, or in the value expression of a computed column.

Important: You must drop the constraint or computed column before dropping the table column. You cannot drop PRIMARY KEY and UNIQUE constraints if they are referenced by FOREIGN KEY constraints. In this case, drop the FOREIGN KEY constraint before dropping the PRIMARY KEY or UNIQUE key it references. Finally, you can drop the column.

Important: When you alter or drop a column, all data stored in it is lost.