Most databases that support default values on their column DDL, it is also possible to actually alter that default. An Oracle example:
CREATE TABLE t ( val NUMBER(7) DEFAULT 1 NOT NULL ); -- Oops, wrong default, let us change it ALTER TABLE t MODIFY val DEFAULT -1; -- Now that is better
Unfortunately, this isn’t possible in SQL Server, where the
DEFAULT column property is really a constraint, and probably a constraint whose name you don’t know because it was system generated.
But luckily, jOOQ 3.4 now supports DDL and can abstract this information away from you by generating the following Transact-SQL program:
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'), 'val', '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 val'; EXECUTE sp_executeSQL @command END ELSE BEGIN SET @command = 'ALTER TABLE t ADD DEFAULT -1 FOR val'; EXECUTE sp_executeSQL @command END
This program will either drop and create a new constraint with the same name, or create an entirely new constraint with a system-generated name.
With jOOQ, you can execute this statement as such:
DSL.using(configuration) .alterTable(T) .alter(T.VAL) .defaultValue(-1) .execute();