The DROP COLUMN statement is part of ALTER TABLE and removes columns from a table.

Tip:

New in v19.1: This command can be combined with other ALTER TABLE commands in a single statement. For a list of commands that can be combined, see ALTER TABLE. For a demonstration, see Add and rename columns atomically.

Synopsis

ALTER TABLE IF EXISTS table_name DROP COLUMN IF EXISTS name CASCADE RESTRICT

Required privileges

The user must have the CREATE privilege on the table.

Parameters

Parameter Description
table_name The name of the table with the column you want to drop.
name The name of the column you want to drop.

When a column with a CHECK constraint is dropped, the CHECK constraint is also dropped.
CASCADE Drop the column even if objects (such as views) depend on it; drop the dependent objects, as well.

CASCADE does not list objects it drops, so should be used cautiously. However, CASCADE will not drop dependent indexes; you must use DROP INDEX.

CASCADE will drop a column with a foreign key constraint if it is the only column in the reference.
RESTRICT (Default) Do not drop the column if any objects (such as views) depend on it.

Viewing schema changes

This schema change statement is registered as a job. You can view long-running jobs with SHOW JOBS.

Examples

Drop columns

If you no longer want a column in a table, you can drop it.

copy
icon/buttons/copy
> ALTER TABLE orders DROP COLUMN billing_zip;

Prevent dropping columns with dependent objects (RESTRICT)

If the column has dependent objects, such as views, CockroachDB will not drop the column by default; however, if you want to be sure of the behavior you can include the RESTRICT clause.

copy
icon/buttons/copy
> ALTER TABLE orders DROP COLUMN customer RESTRICT;
pq: cannot drop column "customer" because view "customer_view" depends on it

Drop column and dependent objects (CASCADE)

If you want to drop the column and all of its dependent options, include the CASCADE clause.

Warning:
CASCADE does not list objects it drops, so should be used cautiously.
copy
icon/buttons/copy
> SHOW CREATE customer_view;
+---------------+----------------------------------------------------------------+
| table_name    | create_statement                                               |
+---------------+----------------------------------------------------------------+
| customer_view | CREATE VIEW customer_view AS SELECT customer FROM store.orders |
+---------------+----------------------------------------------------------------+
copy
icon/buttons/copy
> ALTER TABLE orders DROP COLUMN customer CASCADE;
copy
icon/buttons/copy
> SHOW CREATE customer_view;
pq: view "customer_view" does not exist

See also



Yes No