修改表以修改列的默认值 [英] Alter table to modify default value of column

问题描述

我有一个需要修改数据库表中列默认值的要求.该表已经是数据库中的现有表，并且当前该列的默认值为NULL. 现在，如果将新的默认值添加到此列，如果我是正确的话，它将列的所有现有NULL更新为新的DEfault值.有没有一种方法可以不执行此操作，但仍在列上设置新的默认值. 我的意思是我不想更新现有的NULL，并希望它们保留为NULL.

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

推荐答案

您对将会发生的事情的看法是不正确的.为列设置默认值不会影响表中的现有数据.

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.

我创建一个表，该表的列col2没有默认值

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

这篇关于修改表以修改列的默认值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！