oracle 显式地将列默认为 NULL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32713498/
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
Make column default to NULL explicitly
提问by user1438038
How do I make a column default to NULL explicitly?
如何使列默认为 NULL显式？
I would like to declare a column in Oracle SQL Developer to be NULLon default. I'm aware of the fact, that NULLwill be the default value, if I do not define any default value at all. But how do I define NULLas default, if I would want to do it explicitly?
我想将 Oracle SQL Developer 中的一列声明为NULL默认值。我知道这个事实，NULL如果我根本没有定义任何默认值，那将是默认值。但是NULL，如果我想明确地这样做，我该如何定义为默认值？
-- 1: Does not work.
ALTER TABLE MY_TABLE ADD (
MY_COLUMN TIMESTAMP(6) DEFAULT null
);
-- 2: Does not work.
ALTER TABLE MY_TABLE ADD (
MY_COLUMN TIMESTAMP(6) DEFAULT NULL
);
-- 3: Does not work.
ALTER TABLE MY_TABLE ADD (
MY_COLUMN TIMESTAMP(6) DEFAULT (null)
);
-- 4: This works.
ALTER TABLE MY_TABLE ADD (
MY_COLUMN TIMESTAMP(6)
);
In case 1-3 the default value will be a String ("NULL", "null" or "(null)"), but not an actual NULLvalue. So, what am I missing here?
在情况 1-3 中，默认值将是字符串（“ NULL”、“ null” 或“ (null)”），但不是实际NULL值。那么，我在这里错过了什么？
// Edit:
// 编辑：
Case (a) and (b) correspond to case 1 and 2. A text value of nullor NULLis displayed in SQL Developer. Case (c) corresponds to case 4, where a real(null)value is set explicitly. The screenshots were taken on a table's Columnstab in SQL Developer.
情况 (a) 和 (b) 对应于情况 1 和 2。在 SQL Developer 中显示null或的文本值NULL。情况 (c) 对应于情况 4，其中明确设置了实(null)数值。屏幕截图是在 SQL Developer中表的“列”选项卡上截取的。
SQL Developer http://s1.postimg.org/fclraa0dp/SQL_Developer.png
回答by a_horse_with_no_name
As null, NULLand (null)are the same thing, I don't understand what the problem is.
由于null，NULL和(null)是同一回事，我不明白问题是什么。
It is also not a SQL Developer "problem".
这也不是 SQL Developer 的“问题”。
Oracle simply stores the default expression exactly as you wrote it in the system catalog. SQL Developer simply displays that.
Oracle 只是简单地存储您在系统目录中编写的默认表达式。SQL Developer 只是显示它。
Assume the following statements:
假设有以下语句：
create table my_table (id integer);
alter table my_table add my_column_1 timestamp(6) default null;
alter table my_table add my_column_2 timestamp(6) default null;
alter table my_table add my_column_3 timestamp(6) default (null);
Then
然后
select column_id, column_name, data_type, data_default
from user_tab_columns
where table_name = 'MY_TABLE'
order by column_id;
Will return the following:
将返回以下内容：
COLUMN_ID | COLUMN_NAME | DATA_TYPE | DATA_DEFAULT
----------+-------------+--------------+-------------
1 | ID | NUMBER |
2 | MY_COLUMN_1 | TIMESTAMP(6) | NULL
3 | MY_COLUMN_2 | TIMESTAMP(6) | null
4 | MY_COLUMN_3 | TIMESTAMP(6) | (null)
When you extract the DDL from the system, you again get exactly why you have written:
当您从系统中提取 DDL 时，您又会确切地知道为什么要这样写：
select dbms_metadata.get_ddl('TABLE', 'MY_TABLE', user)
from dual;
returns:
返回：
CREATE TABLE "TK_HIRAC"."MY_TABLE"
( "ID" NUMBER(*,0),
"MY_COLUMN_1" TIMESTAMP (6) DEFAULT NULL,
"MY_COLUMN_2" TIMESTAMP (6) DEFAULT null,
"MY_COLUMN_3" TIMESTAMP (6) DEFAULT (null)
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS NOLOGGING
TABLESPACE "USERS"
回答by Husqvik
Try this:
尝试这个：
HUSQVIK@panel_management> CREATE TABLE MY_TABLE (C1 NUMBER NULL);
Table created.
表已创建。
HUSQVIK@panel_management> ALTER TABLE MY_TABLE ADD (
2 MY_COLUMN TIMESTAMP(6) DEFAULT NULL
3 );
Table altered.
表已更改。
HUSQVIK@panel_management>
works for me.
对我来说有效。
