我的书签
添加书签
移除书签DROP COLUMN
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
Required privileges
The user must have the CREATE privilege on the table.
Parameters
| Parameter | Description |
|---|---|
tablename | 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.
Warning: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
