ALTER TABLE .. ALTER COLUMN .. DROP DEFAULT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
An existing column DEFAULT value can be removed from a column using the ALTER TABLE's DROP DEFAULT clause on a column:
// Drop the default from a column
create.alterTable("table").alter("column").dropDefault().execute();
Dialect support
This example using jOOQ:
alterTable("t").alter("c").dropDefault()
Translates to the following dialect specific expressions:
Access, Aurora Postgres, CockroachDB, DB2, Derby, DuckDB, Exasol, Firebird, H2, HSQLDB, Postgres, Snowflake, Sybase, YugabyteDB
ALTER TABLE t ALTER c DROP DEFAULT
ASE
ALTER TABLE t REPLACE c DEFAULT NULL
Aurora MySQL, MariaDB, MySQL
ALTER TABLE t ALTER COLUMN c SET DEFAULT NULL
BigQuery, Databricks, Vertica
ALTER TABLE t ALTER COLUMN c DROP DEFAULT
ClickHouse
ALTER TABLE t MODIFY COLUMN c REMOVE DEFAULT
MemSQL, Oracle
ALTER TABLE t MODIFY c DEFAULT NULL
SQLServer
DECLARE @constraint NVARCHAR(max);
DECLARE @command NVARCHAR(max);
SELECT @constraint = name
FROM sys.default_constraints
WHERE parent_object_id = object_id('t')
AND parent_column_id = columnproperty(object_id('t'), 'c', 'ColumnId');
IF @constraint IS NOT NULL
BEGIN
SET @command = 'ALTER TABLE ' + 't' + ' DROP CONSTRAINT ' + @constraint
EXECUTE sp_executesql @command
SET @command = 'ALTER TABLE ' + 't' + ' ADD CONSTRAINT ' + @constraint + ' DEFAULT ' + NULL + ' FOR ' + 'c'
EXECUTE sp_executesql @command
END
ELSE
BEGIN
SET @command = 'ALTER TABLE ' + 't' + ' ADD DEFAULT ' + NULL + ' FOR ' + 'c'
EXECUTE sp_executesql @command
END
Hana, Informix, Redshift, SQLDataWarehouse, SQLite, Teradata, Trino
/* UNSUPPORTED */
Generated with jOOQ 3.21. Support in older jOOQ versions may differ. Translate your own SQL on our website
Feedback
Do you have any feedback about this page? We'd love to hear it!