ALTER TABLE ALTER COLUMN DROP NOT NULL

Removes the NOT NULL constraint from a column, allowing null values.

Category: ddl

Syntax

ALTER TABLE <table> ALTER COLUMN <column> DROP NOT NULL

Description

## Overview Removes the NOT NULL constraint from a column, changing it from non-nullable to nullable. This is a metadata-only operation that modifies the Delta schema without rewriting any data files. ## Behavior - The column must exist in the table's current schema. If the column is not found, the command fails with an error. - The operation updates the `nullable` flag in the Delta schema from `false` to `true` for the specified column. - If the column is already nullable, the operation succeeds without error (idempotent). - The COLUMN keyword after ALTER is optional. - No data files are rewritten. Existing data remains unchanged. ## Compatibility This operation is compatible with all Delta readers. Marking a column as nullable is a relaxation of the schema constraint, so older files that contain non-null values for this column remain valid.

Parameters

Name	Type	Description
`table`		Fully qualified table name or registered table name. The table must be registered in the current session.
`column_name`		Column to alter.

Examples

-- Make a column nullable
ALTER TABLE warehouse.sales.orders ALTER COLUMN shipping_address DROP NOT NULL;

-- Drop NOT NULL from a column (without optional COLUMN keyword)
ALTER TABLE warehouse.sales.customers ALTER email DROP NOT NULL;

-- Relax a constraint before a schema migration
ALTER TABLE warehouse.sales.products ALTER COLUMN sku DROP NOT NULL;

Pitfalls

Once a column is made nullable, any new writes may insert NULL values. Downstream queries that assume non-null data for this column should be reviewed.
This command does not affect existing CHECK constraints. If a CHECK constraint enforces non-null values via an expression (e.g., 'col IS NOT NULL'), that constraint remains active even after DROP NOT NULL.