Adding column with empty string as default value and not null constraint leads to inconsistent behaviour for oracle database

Question

I have trouble understanding what happens in the oracle database after this sql is executed:

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?

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

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.

Answer 1

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?

It's not up to the SGBD to verify that your constraints make logical sense. The DEFAULT constraint 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.

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.

SELECT * FROM EMPTYSTRING WHERE COLUMNB='';

SELECT * FROM EMPTYSTRING WHERE COLUMNB IS 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.