SQL 如何从 oracle 中的列中删除默认值？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8481532/
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
How do I remove the default value from a column in oracle?
提问by Danny
A column in a table has a default value of sysdate and I want to change it so it gets no default value, how do I do this?
表中的列的默认值是 sysdate，我想更改它以使其没有默认值，我该怎么做？
回答by Joe Stefanelli
ALTER TABLE YourTable MODIFY YourColumn DEFAULT NULL;
回答by Dan Lenski
Joe's answeris correct in the sense that a column with DEFAULT NULLis functionallyequivalent to having never defined a default value for that column in the first place: if a column has no default value, inserting a new row without specifying a value for that column results in NULL.
乔的回答是在这个意义上正确的，有一列DEFAULT NULL是功能上等同于已经从不定义在首位该列的默认值：如果一列没有默认值，插入新行不指定该列结果的值NULL.
However, Oracle internally represents the two cases distinctly, as can be seen by looking at the ALL_TAB_COLUMNSsystem view. (This applies to Oracle 10.x, 11.x, and 12.x, and probably to older versions as well.)
但是，Oracle 在内部对这两种情况进行了明确的表示，通过查看ALL_TAB_COLUMNS系统视图可以看出。（这适用于 Oracle 10.x、11.x 和 12.x，也可能适用于旧版本。）
The case where a column has been created, or
ALTERed, withDEFAULT NULL:create table foo (bar varchar2(3) default null); select default_length, data_default from all_tab_columns where table_name='FOO'; => default_length data_default -------------- ------------ 4 NULL select dbms_metadata.get_ddl('TABLE','FOO') from dual; => CREATE TABLE "FOO" ( "BAR" VARCHAR(3) DEFAULT NULL … )No default ever specified:
create table foo (bar varchar2(3)); select default_length, data_default from all_tab_columns where table_name='FOO'; => default_length data_default -------------- ------------ (null) (null) select dbms_metadata.get_ddl('TABLE','FOO') from dual; => CREATE TABLE "FOO" ( "BAR" VARCHAR(3) … )
已创建或
ALTER编辑列的情况DEFAULT NULL：create table foo (bar varchar2(3) default null); select default_length, data_default from all_tab_columns where table_name='FOO'; => default_length data_default -------------- ------------ 4 NULL select dbms_metadata.get_ddl('TABLE','FOO') from dual; => CREATE TABLE "FOO" ( "BAR" VARCHAR(3) DEFAULT NULL … )没有指定默认值：
create table foo (bar varchar2(3)); select default_length, data_default from all_tab_columns where table_name='FOO'; => default_length data_default -------------- ------------ (null) (null) select dbms_metadata.get_ddl('TABLE','FOO') from dual; => CREATE TABLE "FOO" ( "BAR" VARCHAR(3) … )
As shown above, there is an important case where this otherwise-meaningless distinction makes a difference in Oracle's output: when using DBMS_METADATA.GET_DDL()to extract the table definition.
如上所示，在一个重要的情况下，这种原本毫无意义的区别DBMS_METADATA.GET_DDL()会对Oracle 的输出产生影响：用于提取表定义时。
If you are using GET_DDL()to introspect your database, then you will get slightly different DDL output for functionally-identicaltables.
如果您正在使用GET_DDL()自省数据库，那么对于功能相同的表，您将获得略有不同的 DDL 输出。
This is really quite annoying when using GET_DDL()for version control and comparison among multiple instances of a database, and there is no way to avoid it, other than to manually modify the output of GET_DDL(), or to completely recreate the table with no default value.
这在GET_DDL()用于版本控制和数据库多个实例之间的比较时真的很烦人，没有办法避免它，除了手动修改 的输出GET_DDL()，或者完全重新创建没有默认值的表。
回答by Brian McGinity
The only way to do what you want is to recreate the table.
做你想做的唯一方法是重新创建表。
It is pretty easy to do in Toad, just right click on the table and select "Rebuild Table". Toad will create script which will rename the table and recreate a new table. The script will recreate indexes, constraints, foreign keys, comments, etc... and populate the table with data.
在 Toad 中很容易做到，只需右键单击表格并选择“重建表格”。Toad 将创建脚本，该脚本将重命名表并重新创建新表。该脚本将重新创建索引、约束、外键、注释等......并用数据填充表。
Just modify the script to remove "default null" after the column in question.
只需修改脚本以删除相关列后的“默认空值”。
