# SQL DROP DEFAULT: Remove a Default Value from a Column

- URL: https://betteranalyst.com/sql/drop-default

SQL guide · Constraints

`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

- [SQL DEFAULT](https://betteranalyst.com/sql/default)
- [SQL NOT NULL](https://betteranalyst.com/sql/not-null)
- [SQL ALTER TABLE](https://betteranalyst.com/sql/alter-table)
- [SQL DROP CONSTRAINT](https://betteranalyst.com/sql/drop-constraint)
- [SQL ADD CONSTRAINT](https://betteranalyst.com/sql/add-constraint)
- [SQL DROP COLUMN](https://betteranalyst.com/sql/drop-column)

[Browse all SQL guides](https://betteranalyst.com/sql)

## 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](https://betteranalyst.com/ai-sql-query-generator)