SQL 更改表以修改列的默认值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21057275/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Alter table to modify default value of column
提问by ravi
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 更新为新的 DEfault 值。有没有办法不这样做但仍然在列上设置新的默认值。我的意思是我不希望更新现有的 NULL 并希望它们保持为 NULL。
Any help on this is appreciated. Thanks
对此的任何帮助表示赞赏。谢谢
回答by Justin Cave
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 col2that 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
回答by Pankaj Kumar
ALTER TABLE {TABLE NAME}
ALTER COLUMN {COLUMN NAME} SET DEFAULT '{DEFAULT VALUES}'
example :
例子 ：
ALTER TABLE RESULT
ALTER COLUMN STATUS SET DEFAULT 'FAIL'
回答by amit
Following Justin's example, the command below works in Postgres:
按照贾斯汀的例子，下面的命令在 Postgres 中工作：
alter table foo alter column col2 set default 'bar';
alter table foo alter column col2 set default 'bar';
回答by VGoudkov
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
在 Oracle Database 12c 企业版 12.2.0.1.0 版上测试
回答by Prema Arya
For Sql Azure the following query works :
对于 Sql Azure，以下查询有效：
ALTER TABLE [TableName] ADD DEFAULT 'DefaultValue' FOR ColumnName
GO
