SQL guide · Constraints

SQL DROP DEFAULT: Remove a Default Value from a Column

DROP DEFAULT removes the default value that was set on a column, so that inserts which omit the column get NULL again, or fail if the column is NOT NULL. It is part of ALTER TABLE and is used when a default is no longer appropriate, when the application should always supply the value explicitly, or when you need to clear the way for a column change or deletion.

Dropping a default only affects future inserts. Rows already in the table keep the value they received when they were created, because the default was copied into them at that moment. If you also want to change the stored data, you must run an UPDATE separately. This makes the operation fast and low risk, since it changes metadata rather than rewriting the table.

The syntax depends on your database. PostgreSQL and MySQL use ALTER COLUMN ... DROP DEFAULT. SQL Server treats a default as a separate named constraint, so you drop it with DROP CONSTRAINT, and you need its name. Oracle has no drop form at all; you modify the column and set the default to NULL. Knowing which one applies avoids confusing errors.

Basic syntax

-- PostgreSQL, MySQL
ALTER TABLE table_name
ALTER COLUMN column_name DROP DEFAULT;

-- SQL Server
ALTER TABLE table_name
DROP CONSTRAINT default_constraint_name;

-- Oracle
ALTER TABLE table_name
MODIFY (column_name DEFAULT NULL);

column_name is the column whose default you are clearing. In SQL Server you drop the default constraint by its own name rather than by column. The column stays in the table with all its data and any other constraints.

SQL DROP DEFAULT examples

Drop a default in PostgreSQL or MySQL

The status column previously defaulted to pending. After the change, inserts must supply a status, or the column is null if it allows nulls. Existing rows are not touched.

ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

Drop a default constraint in SQL Server

SQL Server stores each default as a named constraint. If you named it when creating it, drop it directly. This example assumes it was named df_orders_status.

ALTER TABLE orders
DROP CONSTRAINT df_orders_status;

Find an unnamed SQL Server default

If the default was created without a name, look it up in the system view first. The result gives you the generated name to use in the drop statement, which can be built dynamically if you need to script it.

SELECT dc.name
FROM sys.default_constraints dc
JOIN sys.columns c
  ON c.default_object_id = dc.object_id
WHERE dc.parent_object_id = OBJECT_ID('dbo.orders')
  AND c.name = 'status';

Remove a default in Oracle

Oracle does not have a separate drop clause. Setting the default to NULL removes it. The change leaves existing rows alone, as with every other database.

ALTER TABLE orders
MODIFY (status DEFAULT NULL);

Where SQL dialects differ

PostgreSQL: ALTER TABLE t ALTER COLUMN c DROP DEFAULT is a quick catalog change with no table rewrite. Dropping a default from a serial column leaves the sequence in place; drop the sequence separately if you no longer need it.

MySQL: Use ALTER TABLE t ALTER COLUMN c DROP DEFAULT. Be aware that a NOT NULL column without a default then needs a value on every insert, and strict SQL mode will raise an error if it is missing.

SQL Server: Defaults are constraints, so use DROP CONSTRAINT name. You must drop the default before dropping or altering the type of the column. Unnamed defaults get names like DF__orders__status__3B75D760 that differ by database.

SQLite: There is no ALTER TABLE clause to drop a default. You must create a replacement table without the default, copy the rows in and rename it.

Oracle: Use MODIFY (column DEFAULT NULL). On Oracle 12c and later, a column declared with DEFAULT ON NULL is cleared the same way.

Common mistakes

  • Expecting DROP DEFAULT to change stored data. Rows that already received the default keep it, so run an UPDATE if needed.
  • Using the PostgreSQL syntax in SQL Server. There you must know and drop the default constraint by name.
  • Dropping a default from a NOT NULL column and then not updating the application. Inserts that relied on the default will now fail.
  • Trying to drop a column in SQL Server while its default constraint still exists. Drop the default constraint first.

Related SQL guides

Browse all SQL guides

Need help writing the SQL?

Describe your tables and the result you want, then review the generated query against your database before running it.

Try the AI SQL generator