O'Reilly Report: Why Distributed SQL Is the Modern Foundation for AI. (Click for Free Download)

SQL DROP COLUMN and ADD COLUMN

SQL ADD COLUMN and DROP COLUMN are the two most common ways to modify the structure of an existing table without recreating it from scratch. 

Whether you’re extending a schema to support a new feature or cleaning up deprecated fields, understanding how these operations work in production is worth getting right.

What Is the SQL ALTER TABLE Statement?

ADD COLUMN and DROP COLUMN are both clauses of the ALTER TABLE statement, which changes a table’s structure while leaving its existing data intact. This behavior is standard SQL, consistent with PostgreSQL, and fully supported in YugabyteDB’s YSQL API.

When Would You Add or Drop a Column?

You add a column when your application needs to capture new data, such as a new attribute, a flag, or a foreign key reference, without rebuilding the table. You drop a column when a field is deprecated, no longer populated, or being replaced by a different schema design. In both cases, the goal is to evolve the schema in place rather than migrating data to a new table.

How Do You Add a Column in SQL?

The basic syntax for adding a column is:

ALTER TABLE table_name
ADD COLUMN column_name data_type [constraints];


The new column is appended after all existing columns. Key behaviors to know:

  • Existing rows receive NULL for the new column unless a DEFAULT value is specified.
  • IF NOT EXISTS prevents an error if the column already exists.
  • Multiple columns can be added in a single statement by separating definitions with commas.

Example — adding a department column to an employees table:

ALTER TABLE employees
ADD COLUMN department VARCHAR(100) DEFAULT 'Unassigned';\

What Happens to Existing Rows When a Column Is Added?

Existing rows receive NULL (or the specified DEFAULT). Adding a column with a static default is a fast, metadata-only operation in YugabyteDB. No row data is rewritten. Adding a column with a volatile default value (such as gen_random_uuid()) requires a full table rewrite, which can take significantly longer on large tables. Plan accordingly before running this in production. 

How Do You Drop a Column in SQL?

The syntax for dropping a column is:

ALTER TABLE table_name
DROP COLUMN column_name [CASCADE | RESTRICT];

A few important points:

  • DROP COLUMN is permanent; the column and all its data are removed
  • RESTRICT is the default; it blocks the drop if other objects (foreign keys, views, indexes) depend on the column
  • CASCADE removes the column and automatically drops all dependent objects
  • IF EXISTS prevents an error if the column does not exist
  • PRIMARY KEY columns cannot be dropped via DROP COLUMN in YugabyteDB

Example — removing the department column added above:

ALTER TABLE employees
DROP COLUMN IF EXISTS department;

What Is the Difference Between CASCADE and RESTRICT When Dropping a Column?

RESTRICT surfaces dependency problems before any data is removed. It’s the safe default. If another object references the column, the statement fails, and nothing is dropped. CASCADE removes the column and silently drops everything that depends on it, including views, indexes, and constraints. Before using CASCADE in production, audit which objects depend on the column to avoid unintended data loss.

How Does YugabyteDB Handle ADD COLUMN and DROP COLUMN?

YugabyteDB supports online schema changes, meaning most ADD COLUMN and DROP COLUMN operations complete without taking the table offline or blocking concurrent reads and writes. Here’s how each case behaves:

  • ADD COLUMN with no default or a static default: fast, metadata-only operation, no table rewrite
  • ADD COLUMN with a volatile default: requires a full table rewrite; plan for longer execution on large tables
  • DROP COLUMN: fast in most cases; data becomes logically inaccessible immediately, with physical reclamation happening in the background
  • YCQL: ADD COLUMN sets existing rows to NULL; DROP COLUMN discards all stored column data immediately

YugabyteDB also has transactional DDL in Tech Preview, which lets you mix DDL and DML operations in the same transaction block and roll both back together. It’s disabled by default and requires enabling a preview flag, and it currently has real limitations: concurrent DDL against the same database isn’t supported, and savepoints can’t be combined with DDL in the same transaction. For teams that want to combine schema changes and data migrations atomically today, test this feature’s current limitations against your specific workflow before relying on it in production.

ADD COLUMN and DROP COLUMN work as expected in YugabyteDB, with online schema change support covering the majority of real-world operations.

For the complete syntax reference, including column constraints, type modifiers, and YCQL-specific behavior, see the YugabyteDB YSQL ALTER TABLE docs.