The DROP COLUMN
statement is part of ALTER TABLE
and removes columns from a table.
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
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.
> 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 and dependent objects (CASCADE
)
If you want to drop the column and all of its dependent options, include the CASCADE
clause.
CASCADE
does not list objects it drops, so should be used cautiously.> SHOW CREATE customer_view;
+---------------+----------------------------------------------------------------+
| table_name | create_statement |
+---------------+----------------------------------------------------------------+
| customer_view | CREATE VIEW customer_view AS SELECT customer FROM store.orders |
+---------------+----------------------------------------------------------------+
> ALTER TABLE orders DROP COLUMN customer CASCADE;
> SHOW CREATE customer_view;
pq: view "customer_view" does not exist