Alter table to modify default value of column

Question

I have a requirement where we need to modify a column's default value in database table. The table is already an existing table in database and currently the default value of the column is NULL. Now if add a new default value to this column, If I am correct it updates all the existing NULLs of the column to new DEfault value. Is there a way to not to do this but still set a new default value on column. I mean I do not want the existing NULLs to be updated and want them to remain as NULLs.

Any help on this is appreciated. Thanks

what is the datatype of your column ? – M.Ali Jan 11 '14 at 01:13 — M.Ali, Jan 11 '14 at 01:13

score 70 · Accepted Answer · answered Jan 11 '14 at 02:45

Your belief about what will happen is not correct. Setting a default value for a column will not affect the existing data in the table.

I create a table with a column col2 that has no default value

SQL> create table foo(
  2    col1 number primary key,
  3    col2 varchar2(10)
  4  );

Table created.

SQL> insert into foo( col1 ) values (1);

1 row created.

SQL> insert into foo( col1 ) values (2);

1 row created.

SQL> insert into foo( col1 ) values (3);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

If I then alter the table to set a default value, nothing about the existing rows will change

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'foo' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

SQL> insert into foo( col1 ) values (4);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

Even if I subsequently change the default again, there will still be no change to the existing rows

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'bar' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

SQL> insert into foo( col1 ) values (5);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo
         5 bar

Good example. As a trivial shortcut, the datatype isn't needed when only adding a default. You can just do `alter table foo modify (col2 default 'bar')` — rimsky, Apr 07 '15 at 17:14
@rimsky Neat shortcut but https://dev.mysql.com/doc/refman/5.7/en/alter-table.html#alter-table-redefine-column appears to suggest otherwise. Perhaps the documentation is misleading. It also mentions `ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}`. — trss, Aug 14 '17 at 07:09
@trss you are quoting from MySQL documentation, but the question is tagged `oracle` — osullic, Oct 29 '18 at 16:34
Sorry, my bad. Didn't notice those tiny tags. Will be helpful if the question mentions Oracle too. — trss, Oct 30 '18 at 17:35

score 10 · Answer 2 · edited Aug 14 '20 at 07:52

10

ALTER TABLE *table_name*
MODIFY *column_name* DEFAULT *value*;

worked in Oracle

e.g:

ALTER TABLE MY_TABLE
MODIFY MY_COLUMN DEFAULT 1;

edited Aug 14 '20 at 07:52

HK boy

1,398
11
17
25

answered Aug 14 '20 at 03:09

skk

151
1
2
9

score 2 · Answer 3 · edited Mar 01 '16 at 05:44

2

ALTER TABLE {TABLE NAME}
ALTER COLUMN {COLUMN NAME} SET DEFAULT '{DEFAULT VALUES}'

example :

ALTER TABLE RESULT
ALTER COLUMN STATUS SET DEFAULT 'FAIL'

edited Mar 01 '16 at 05:44

sKhan

9,694
16
55
53

answered Mar 01 '16 at 05:37

Pankaj Kumar

57
4

For some reason this raises ORA-01735 error: Alter Table MGRA.TAB1 ALTER COLUMN ID_PRO SET DEFAULT 1; – Magier Jan 25 '18 at 18:23
I think this is T-SQL (MS SQL), not Oracle. – Mike Oct 18 '19 at 09:46

score 2 · Answer 4 · answered Oct 25 '16 at 08:24

2

Following Justin's example, the command below works in Postgres:

alter table foo alter column col2 set default 'bar';

answered Oct 25 '16 at 08:24

amit kumar

20,438
23
90
126

score 2 · Answer 5 · answered Mar 05 '20 at 14:40

2

ALTER TABLE <table_name> MODIFY <column_name> DEFAULT <defult_value>

EX: ALTER TABLE AAA MODIFY ID DEFAULT AAA_SEQUENCE.nextval

Tested on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0

answered Mar 05 '20 at 14:40

VGoudkov

21
1

score -2 · Answer 6 · edited Mar 27 '18 at 23:18

-2

For Sql Azure the following query works :

ALTER TABLE [TableName] ADD  DEFAULT 'DefaultValue' FOR ColumnName
GO

edited Mar 27 '18 at 23:18

K.Dᴀᴠɪs

9,945
11
33
43

answered Mar 27 '18 at 22:58

Prema Arya

69
1
4

Alter table to modify default value of column

6 Answers6

Linked