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.

Tarter

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.

Pentameter

SELECT * FROM EMPTYSTRING WHERE COLUMNB IS NULL; returns no rows, as stated in the queston. – Tarter

Also, to my understanding, when new column is added with default value and the not null constraint, the default value should be added not only to all new records, but also to the already existing records. Therefore if '' is null for varchar2 then the error should pop up when COLUMNB is added. – Tarter

when new column is added with default value and the not null constraint, the default value is nopt added to the old records, it is applied to the new records (except the not null constraint which check if the old records have or no a null vlaues). – Pentameter

The old records can have non-null value in a column that didn't exist? That's a bit odd, don't you think? – Tarter

Answer 2

I ran your first 3 SQL statements and on running the 3rd one to add the column with DEFAULT '' NOT NULL, I got this error:

[42000][1758] ORA-01758: table must be empty to add mandatory (NOT NULL) column Position: 12

Also if I modify this ALTER statement to DEFAULT 'H' NOT NULL, then the previous NULL entries in the table will have the H value, unlike what @Blood-HaZaRd wrote in the comment.

Secondly, if I insert into a nullable column either NULL or '', this SQL returns all results, as @Blood-HaZaRd mentions in his answer:

SELECT * FROM EMPTYSTRING WHERE COLUMNB IS NULL;

My version of Oracle is gvenzl/oracle-xe:21.3.0-slim.

Tarrsus