-196 COLUMN table-name.column-name CANNOT BE DROPPED. REASON = reason-code.

Explanation

An attempt was made to drop the specified column, but the column cannot be dropped for the indicated reason.

table-name
The name of the table.
column-name
The name of the column.
reason-code
A numeric value that indicates the reason that the statement cannot be executed.
1
The column cannot be dropped because it is part of the hash key or partitioning key.
3
The column cannot be dropped because it is a security label column.
4
The column cannot be dropped because it is part of a period.
5
The column cannot be dropped because the table is a system-period temporal table or archive-enabled table.
6
The column cannot be dropped because it is a column in a history table or archive table.
8
The table, table-name, is defined with an edit procedure or validation procedure.
9
The table, table-name, is referenced (directly or indirectly) in the definition of a materialized query table.
10
A check constraint is defined on the table, table-name.
11
An extended index is defined on the table, table-name.
12
A trigger is defined on the table, table-name, or an INSTEAD OF trigger is defined on a view that is dependent on the table.
13
A row permission is dependent on the table, table-name.
14
A column mask is dependent on the table, table-name.
15
An SQL table function is dependent on the table, table-name.

System action

The statement cannot be processed.

Programmer response

Take the action that corresponds to the reason-code value:

1
  • If the column is part of a partitioning key, drop the table and create it again without the column that needs to be dropped and redefine the partitioning key.
  • If the column is part of a hash key:
    1. Alter the table to remove the hash organization.
    2. Drop the column.
    3. Run the REORG utility on the containing table space so the drop column operation takes effect.
    4. Alter the table to add the hash organization.
    As an alternative, consider dropping the table and creating it again without the column that needs to be dropped.
3
Drop the table and create it again, without creating the security label column.
4
Drop the table and create it again, without creating the column that needs to be dropped.
5
Alter the table to remove system-period data versioning or disable archiving. (Issue ALTER TABLE with the DROP VERSIONING or DISABLE ARCHIVE clause.) Then drop the column from the table.

If system-period versioning or archiving is still needed, drop the column from the previously associated history table or archive table. Then add system-period data versioning or archiving back to the table.

6
Drop versioning or archiving so that the table is no longer a history table or an archive table. (Use the ALTER TABLE statement with the DROP VERSIONING or DISABLE ARCHIVE clause on the associated system-period temporal table or archive-enabled table.) Then drop the column from the table.

If versioning or archiving is still needed, drop the column from the system-period temporal table or archive-enabled table. Then add versioning or archiving back to this table.

8
If the table is defined with a validation procedure, alter the table to remove the valid procedure and then drop the column. If the table is defined with an edit procedure, drop the table and create it again without the column that needs to be dropped.
9
Drop any materialized query tables that reference the table, then drop the column.
10
Drop and check constraints that reference the table, then drop the column.
11
Drop any extended indexes that reference the table, then drop the column.
12
Drop any triggers that reference the table or dependent views, then drop the column.
13
Drop any row permissions that reference the table, then drop the column.
14
Drop any column masks that reference the table, then drop the column.
15
Drop any SQL table functions that reference the table, then drop the column.

SQLSTATE

42817