The following rules and restrictions apply to dropping columns from a table:
- You cannot drop all of the columns from a table.
- When you drop a column, the database deletes the field corresponding to the dropped column in every row in the table.
- You cannot drop indexed columns from a table without first dropping the index on those columns.
The following set of procedures explain what you must do to drop an indexed column from a table.
|To drop a column on which this type of index is defined …||Follow this procedure …|
|Primary: the table has no rows.||
|Primary: using the CREATE TABLE AS statement||
|Primary: legacy method||
- You cannot drop a partitioning column for a row-partitioned table without first modifying the partitioning to exclude that column from the partitioning set. Otherwise, the database returns an error.
You cannot drop a column on which a primary, primary AMP index, secondary, hash, or join index is defined.
- You cannot drop columns that are referenced in the UPDATE OF clause of a trigger.
- You can drop the identity column from an existing table.
- You can also drop only the identity column attribute from an identity column in an existing table, retaining the column and its data.
- You can drop LOB columns from a base table without restrictions. If you drop all the LOB columns from a table, the table is not bound by the LOB restrictions. See Rules and Restrictions for Large Object Data Types.
- You cannot drop the QITS column from a queue table.