添加具有空字符串作为默认值且非空约束的列会导致 oracle 数据库的行为不一致
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22091857/
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
Adding column with empty string as default value and not null constraint leads to inconsistent behaviour for oracle database
提问by 01tomislav
I have trouble understanding what happens in the oracle database after this sql is executed:
我无法理解执行此 sql 后 oracle 数据库中会发生什么：
CREATE TABLE EMPTYSTRING
(
COLUMNA VARCHAR2(1)
);
INSERT INTO EMPTYSTRING (COLUMNA) VALUES('X');
ALTER TABLE EMPTYSTRING ADD
(
COLUMNB VARCHAR2(1) DEFAULT '' NOT NULL
);
As I read, oracle treats empty strings as nulls. But then why is adding a column with a default "null" value and a constraint that says that values cannot be null legal?
正如我所读到的，oracle 将空字符串视为空值。但是，为什么要添加具有默认“空”值的列和说明值不能为空合法的约束？
More importantly, since it is legal, how is this treated internaly? If we try
更重要的是，既然是合法的，内部又是如何处理的呢？如果我们尝试
SELECT * FROM EMPTYSTRING WHERE COLUMNB='';
SELECT * FROM EMPTYSTRING WHERE COLUMNB IS NULL;
we get no results. On the other hand, if we try
我们没有结果。另一方面，如果我们尝试
SELECT * FROM EMPTYSTRING;
SELECT * FROM EMPTYSTRING WHERE TRIM(COLUMNB) IS NULL;
we get:
我们得到：
COLUMNA COLUMNB
------- -------
X
So what is really written in the database? Why does it behave this way?
那么数据库中真正写的是什么呢？为什么会这样？
And if we try to insert a row without specifying a value for COLUMNB
如果我们尝试插入一行而不为 COLUMNB 指定值
INSERT INTO EMPTYSTRING (COLUMNA) VALUES('Y');
we get "cannot insert NULL" error so defaulting to empty string doesn't really work except for the rows that were in the table before we added COLUMNB.
我们收到“无法插入 NULL”错误，因此默认为空字符串实际上不起作用，除了在我们添加 COLUMNB 之前表中的行。
回答by Blood-HaZaRd
ALTER TABLE EMPTYSTRING ADD ( COLUMNB VARCHAR2(1) DEFAULT '' NOT NULL );As I read, oracle treats empty strings as nulls. But then why is adding a column with a default "null" value and a constraint that says that values cannot be null legal?
ALTER TABLE EMPTYSTRING ADD ( COLUMNB VARCHAR2(1) DEFAULT '' NOT NULL );正如我所读到的，oracle 将空字符串视为空值。但是，为什么要添加具有默认“空”值的列和说明值不能为空合法的约束？
It's not up to the SGBD to verify that your constraints make logical sense. The DEFAULTconstraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. Oracle does not check whether the default value complies with other constraints, so there will be no error signaled by Oracle if you write your constraint in a such way (there will be no ORA Error messages). The problem comes when you try to insert a record without defining the value of COLUMNB.
SGBD 无法验证您的约束是否符合逻辑。该DEFAULT约束用来插入一个默认值成一列。如果未指定其他值，则默认值将添加到所有新记录中。Oracle 不会检查默认值是否符合其他约束，因此如果您以这种方式编写约束，Oracle 将不会发出错误信号（不会有 ORA 错误消息）。当您尝试在没有定义COLUMNB值的情况下插入记录时，问题就出现了。
First it will try to give '' (which is NULL) to COLUMNB, and then it will apply the constraint defined for the COLUMB (which is make sure that the value is not NULL). This will cause the error ORA-01407, saying that cannot update(...) to NULL.
首先它会尝试给 COLUMNB 提供 ''（它是 NULL），然后它会应用为 COLUMB 定义的约束（确保该值不是 NULL）。这将导致错误ORA-01407，说不能 update(...) 为 NULL。
SELECT * FROM EMPTYSTRING WHERE COLUMNB='';
SELECT * FROM EMPTYSTRING WHERE COLUMNB IS NULL;
SELECT * FROM EMPTYSTRING WHERE COLUMNB='';
SELECT * FROM EMPTYSTRING WHERE COLUMNB 为 NULL;
In Oracle we can not write sth = null (to mean if it is null or not). If we have two null values, they are still different (null is defined as unequal to itself). The only way to check whether COLUMNB is null is by using IS NULL. So the first one would return no rows, even if COLULMNB actually did have null values. The second one would return rows with null values for COLUMNB, but because of the NOT NULL constraint, no such rows exist.
在 Oracle 中我们不能写 sth = null （表示它是否为 null）。如果我们有两个空值，它们仍然不同（空被定义为不等于自身）。检查 COLUMNB 是否为空的唯一方法是使用IS NULL。所以第一个不会返回任何行，即使 COLULMNB 实际上确实有空值。第二个将返回 COLUMNB 为空值的行，但由于 NOT NULL 约束，不存在这样的行。
