Insert empty string in Oracle [duplicate]

Question

I'm trying to insert an empty string in a non-nullable column in Oracle but fails. Here's the case:

create table trademark (
  name varchar2(100) not null
);

insert into trademark (name) values ('Kodak');

insert into trademark (name) values (' '); -- one space

insert into trademark (name) values (''); -- empty string
Error: ORA-01400: cannot insert NULL into ("USER1"."TRADEMARK"."NAME")

What am I doing wrong?

Answer 1

In Oracle, an empty string is equivalent to NULL.

In almost any other database, the two are different, but that is how Oracle defines NULL values for strings (by default).

This is explained in the documentation, along with this enticing note:

Note:

Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

The highlighted portion is mine. I'm not sure how you are supposed to follow that recommendation. I think it means to use NULL explicitly, rather than '', when you intend NULL.

Note that in SQL, NULL represents an unknown value, not an empty value. There is a big difference between a string that has no characters (a perfectly valid string) and a NULL value which is unknown. In practice, NULL is often used for missing, but that is more of a convention than a definition.