How Dropping a Column Affects Constraints

When you drop a column, all constraints on that column are also dropped, unless the same ALTER TABLE statement also adds the same column in the same order within the table schema.

Dropping a column has these effects on its constraints:

All single-column constraints are dropped.
All referential constraints that reference the column are dropped.
All check constraints that reference the column are dropped.
If the column is part of a multiple-column primary-key or unique constraint, the constraints that are placed on the multiple columns are also dropped. This action, in turn, triggers the dropping of all referential constraints that reference the multiple columns.
The syscolumns and sysconstraints system catalog tables are updated to remove all the rows corresponding to the dropped columns and the dropped constraints.

Because any constraints that are associated with a column are dropped when the column is dropped, the structure of other tables might also be altered when you use this clause. For example, if the dropped column is a unique or primary key that is referenced in other tables, those referential constraints also are dropped. Therefore, the structure of those other tables is also altered.

Dropping columns without changing the table schema

The same ALTER TABLE statement can include both a DROP Column clause that drops a column (or drops several columns), followed by an ADD Column clause that adds columns of the same name, data type, and ordinal position among the columns of the table. In this case, the database server takes no action to reorganize the schema of the table, or to drop constraints that are defined on the columns that were dropped and restored in the same ALTER TABLE statement. That is, nothing happens to the table or to its data.

For example, suppose that table UnsTable is created with five columns:

CREATE TABLE IF NOT EXISTS UnsTable (
   col1 CHAR(18),
   col2 INT NOT NULL,
   col3 CHAR(32) UNIQUE,
   col4 INT NOT NULL,
   col5 DATETIME YEAR TO MONTH);

The following ALTER TABLE statement instructs the database server to drop both INTEGER columns col2 and col4, and also to add two columns with the same names and data type:

ALTER TABLE UnsTable (
   DROP (col2, col4)
   ADD (col2 INT NOT NULL BEFORE col3, 
        col4 INT NOT NULL BEFORE col5);

This ALTER TABLE statement has no effect on the schema or the data in the UnsTable table, nor on any existing constraints that reference those columns, because the database server takes no action. Therefore, DDL statements that resemble the example that are generated by analytic tools do not affect the database server or the data.

When you need to reorganize a table and drop constraints by replacing one or more columns with new columns that have the same names, the same data types, and the same ordinal positions within the table schema, run two separate ALTER TABLE statements, as in the following example:

ALTER TABLE UnsTable (
   DROP (col2, col4);

ALTER TABLE UnsTable (
   ADD (col2 INT NOT NULL BEFORE col3, 
        col4 INT NOT NULL BEFORE col5);

The ALTER TABLE DROP statement drops any existing constraints that reference columns col2 or col4 of the UnsTable table.