On this page
Warning:
As of October 4, 2019, CockroachDB v2.0 is no longer supported. For more details, refer to the Release Support Policy.
The DROP COLUMN
statement is part of ALTER TABLE
and removes columns from a table.
Synopsis
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 .New in v2.0: 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
Whenever you initiate a schema change, CockroachDB registers it as a job, which you can view with SHOW JOBS
.
Examples
Drop Columns
If you no longer want a column in a table, you can drop it.
> 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.
> ALTER TABLE orders DROP COLUMN customer RESTRICT;
pq: cannot drop column "customer" because view "customer_view" depends on it
Drop Column & 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.> SHOW CREATE VIEW customer_view;
+---------------+----------------------------------------------------------------+
| View | CreateView |
+---------------+----------------------------------------------------------------+
| customer_view | CREATE VIEW customer_view AS SELECT customer FROM store.orders |
+---------------+----------------------------------------------------------------+
> ALTER TABLE orders DROP COLUMN customer CASCADE;
> SHOW CREATE VIEW customer_view;
pq: view "customer_view" does not exist