SQL DROP DEFAULT

What does SQL DROP DEFAULT do?

Removes an existing default value from a table column so future inserts use NULL or require explicit values.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Compatible dialects for SQL DROP DEFAULT: PostgreSQL, MySQL, MariaDB, SQL Server (via constraint), Oracle, DB2. Not natively supported in SQLite (requires table rebuild).

SQL DROP DEFAULT Full Explanation

DROP DEFAULT is used inside an ALTER TABLE statement to eliminate the default expression previously associated with a column. After the default is dropped, any subsequent INSERT that omits the column will either store NULL (if the column allows it) or raise an error for missing data. The exact command syntax and limitations vary by dialect:- Standard SQL and PostgreSQL let you say ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT.- MySQL accepts ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT, but you can also use MODIFY column DEFAULT NULL.- SQL Server stores defaults in named constraints, so you must first identify and drop the constraint with ALTER TABLE ... DROP CONSTRAINT.- Oracle uses ALTER TABLE ... MODIFY column DEFAULT NULL.Dropping the default does not affect existing rows; it only changes behavior for rows inserted afterward. If a default was defined by a sequence or function, dropping it does not remove those objects.

SQL DROP DEFAULT Syntax

--Standard SQL / PostgreSQL
ALTER TABLE table_name
ALTER COLUMN column_name
DROP DEFAULT;

--MySQL
ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;
--or
ALTER TABLE table_name
MODIFY column_name column_type DEFAULT NULL;

--SQL Server (two-step)
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

SQL DROP DEFAULT Parameters

Example Queries Using SQL DROP DEFAULT

--PostgreSQL example
ALTER TABLE users
ALTER COLUMN created_at DROP DEFAULT;

--MySQL example
ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

--SQL Server example (find and drop constraint)
DECLARE @cname sysname;
SELECT @cname = dc.name
FROM sys.default_constraints dc
JOIN sys.columns c ON dc.parent_object_id = c.object_id AND dc.parent_column_id = c.column_id
WHERE OBJECT_NAME(dc.parent_object_id) = 'payments' AND c.name = 'processed_at';
EXEC('ALTER TABLE payments DROP CONSTRAINT ' + @cname);

Expected Output Using SQL DROP DEFAULT

The default expression attached to the specified column is removed
Existing data stays unchanged
Future INSERT statements that omit the column will no longer receive the former default value

Use Cases with SQL DROP DEFAULT

Retire a sequence or function that is no longer appropriate as a default.
Enforce that applications always supply an explicit value instead of relying on a fallback.
Prepare for a data-type change that is incompatible with the current default.

Common Mistakes with SQL DROP DEFAULT

Forgetting that SQL Server requires dropping the default constraint, not using DROP DEFAULT syntax.
Assuming existing rows will be updated—DROP DEFAULT only affects new inserts.
Omitting ALTER COLUMN in PostgreSQL/MySQL, which leads to syntax errors.

First Introduced In

SQL-92 standard (ALTER TABLE enhancements)

Frequently Asked Questions

How do I remove a default value from a column?

Use ALTER TABLE with DROP DEFAULT. In PostgreSQL: `ALTER TABLE tbl ALTER COLUMN col DROP DEFAULT;`.

Will dropping a default modify existing rows?

No. Only future INSERT statements are affected. Current data remains intact.

How do I drop a default in SQL Server?

Identify the default constraint name (from sys.default_constraints) and run `ALTER TABLE table_name DROP CONSTRAINT constraint_name;`.

Can I revert the change later?

Yes. Use ALTER TABLE ... ALTER COLUMN ... SET DEFAULT expression to add a new default at any time.