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.
我需要修改数据库表中列的默认值。该表已经是数据库中的现有表,当前列的默认值为NULL。现在,如果向该列添加一个新的默认值,如果我是正确的,那么它会将该列的所有现有空值更新为新的默认值。是否有办法不这样做,但仍然在列上设置新的默认值。我的意思是,我不希望更新现有的null,希望它们保持为NULLs。
Any help on this is appreciated. Thanks
如有任何帮助,我们将不胜感激。谢谢
4 个解决方案
#1
40
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
我用一个没有默认值的列col2创建一个表。
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
#2
2
ALTER TABLE {TABLE NAME}
ALTER COLUMN {COLUMN NAME} SET DEFAULT '{DEFAULT VALUES}'
example :
例子:
ALTER TABLE RESULT
ALTER COLUMN STATUS SET DEFAULT 'FAIL'
#3
1
Following Justin's example, the command below works in Postgres:
以Justin为例,下面的命令在Postgres中工作:
alter table foo alter column col2 set default 'bar';
更改表foo alter列col2设置默认的'bar';
#4
-1
For Sql Azure the following query works :
对于Sql Azure,下面的查询工作如下:
ALTER TABLE [TableName] ADD DEFAULT 'DefaultValue' FOR ColumnName
GO
#1
40
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
我用一个没有默认值的列col2创建一个表。
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
#2
2
ALTER TABLE {TABLE NAME}
ALTER COLUMN {COLUMN NAME} SET DEFAULT '{DEFAULT VALUES}'
example :
例子:
ALTER TABLE RESULT
ALTER COLUMN STATUS SET DEFAULT 'FAIL'
#3
1
Following Justin's example, the command below works in Postgres:
以Justin为例,下面的命令在Postgres中工作:
alter table foo alter column col2 set default 'bar';
更改表foo alter列col2设置默认的'bar';
#4
-1
For Sql Azure the following query works :
对于Sql Azure,下面的查询工作如下:
ALTER TABLE [TableName] ADD DEFAULT 'DefaultValue' FOR ColumnName
GO