KDBS
  1. Docs
  2. SQL reference

ALTER TABLE, DROP TABLE

v3.0.0

Rename a table or a column, or drop a table and reclaim its pages.

In this article

ALTER TABLE renames a table or a column. DROP TABLE removes a table. Both change the catalog only: a rename rewrites no row, and a drop leaves the rows untouched until its pages are reclaimed after commit.

Syntax

ALTER TABLE <table> RENAME TO <new_name>
ALTER TABLE <table> RENAME COLUMN <column> TO <new_name>

DROP TABLE <table>

Examples

ALTER TABLE orders RENAME COLUMN note TO memo;

RENAMED COLUMN orders.note TO memo

DROP TABLE orders;

DROPPED TABLE orders oid=4038

ALTER TABLE

  • A table is identified internally by a number, not by its name, so after a rename every foreign key, index and Cabin keeps working without being declared again.
  • The primary-key column can be renamed like any other.
  • A table that has an assertion cannot be renamed, and neither can its columns. Drop the assertion, rename, and declare it again.
  • ALTER TABLE is not transactional: ROLLBACK does not undo it.
  • Statistics the engine recorded for the old name stop matching. That can make queries slower until new traffic is recorded; it never changes a result.

DROP TABLE

  • The name is free immediately. The table’s internal number is never reused.
  • The table’s indexes, Cabins, columns and the foreign keys it declares as a child are dropped with it.
  • RESTRICT: a table that another table references with a foreign key cannot be dropped, even if no child row exists. A table with an assertion cannot be dropped either. Drop those first.
  • DROP TABLE waits for statements that are already reading the table and for open transactions that have written to it. If it waits longer than lock_wait_fault_net_ms (default 1 second), it is refused with a retryable TXN_CONFLICT.
  • Inside a transaction, ROLLBACK restores the table and its rows.
  • The table’s pages return to the allocator after the drop commits and the log has moved past it, usually within a checkpoint or two. The data file never shrinks; later tables reuse the space.

Limitations

  • ADD COLUMN, DROP COLUMN and type changes are refused with NOT_IMPLEMENTED. Every row has a fixed size, so changing columns means rewriting the table.
  • DROP TABLE is not isolated. Between the DROP and its COMMIT or ROLLBACK, other sessions already see the table as gone. If the transaction rolls back, it comes back. The rows are never at risk.
  • A system table (sys.*) cannot be renamed or dropped.

See also