Available in versions: Dev (3.21) | Latest (3.20) | 3.19 | 3.18 | 3.17 | 3.16 | 3.15 | 3.14 | 3.13 | 3.12 | 3.11
ALTER TABLE .. ALTER COLUMN .. SET DEFAULT
Applies to ✅ Open Source Edition ✅ Express Edition ✅ Professional Edition ✅ Enterprise Edition
A column DEFAULT value can be added to a column using the ALTER TABLE's SET DEFAULT clause on a column:
// Specify a new default value for a column
create.alterTable("table").alter("column").setDefault(1).execute();
Dialect support
This example using jOOQ:
alterTable("t").alter("c").setDefault(1)
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 SET DEFAULT 1
ASE
ALTER TABLE t REPLACE c DEFAULT 1
Aurora MySQL, BigQuery, Databricks, MariaDB, MySQL, Vertica
ALTER TABLE t ALTER COLUMN c SET DEFAULT 1
ClickHouse
ALTER TABLE t MODIFY COLUMN c DEFAULT 1
MemSQL, Oracle
ALTER TABLE t MODIFY c DEFAULT 1
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 ' + '1' + ' FOR ' + 'c'
EXECUTE sp_executesql @command
END
ELSE
BEGIN
SET @command = 'ALTER TABLE ' + 't' + ' ADD DEFAULT ' + '1' + ' 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!