5

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?

The Impaler
  • 45,731
  • 9
  • 39
  • 76
  • Yep, this is still valid in Oracle 12c as it was in Oracle 9i (the other question). – The Impaler Apr 05 '18 at 18:05
  • What is the point of a `not null` constraint on Trademark Name if you are just going to enter a non-value? Was it the schema designer's intention that although you must supply a name, it's OK if it's `''`? It seems like that is still not entering a trademark name. (That's aside from the point that Oracle doesn't have `''` as a separate value, as already mentioned in answers below.) – William Robertson Apr 06 '18 at 09:07

1 Answers1

12

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Note that while Oracle's behavior is non-standard, storing empty strings in a database is a bad, bad practice. – David Browne - Microsoft Apr 05 '18 at 17:29
  • 3
    Thank goodness for Oracle staying sane and not having two kinds of nothing. – William Robertson Apr 05 '18 at 17:29
  • @DavidBrowne-Microsoft . . . I disagree. An empty string is an empty string; I don't see anything "bad, bad" about that *per se*. – Gordon Linoff Apr 05 '18 at 17:30
  • Storing empty strings alongside nulls introduces a business distinction between two kinds of whitespace which may or may not appear identical depending on the reporting interface. – William Robertson Apr 05 '18 at 17:33
  • Also it always seems to me that entering an empty string in a mandatory column is rather against the spirit of the `not null` constraint. Say the data model requires a customer to have a last name. OK, I set it to `''`. Problem solved? – William Robertson Apr 05 '18 at 17:37
  • @DavidBrowne-Microsoft - Would you care to state your reasons for your opinion? I doubt that it is shared by a majority of professionals. Surely the writers of the SQL Standard disagree. –  Apr 05 '18 at 17:42
  • 8
    @WilliamRobertson - That is nonsense. Neither `NULL` nor an empty string are "whitespace". If you meant "undetermined" that is simply not true; `NULL` is undetermined, while the empty string isn't. The empty string should be allowed to be the neutral element for concatenation (same as 0 is for addition of numbers). Length of the empty string should be 0, length of `NULL` should be `NULL`. –  Apr 05 '18 at 17:46
  • 2
    A common argument is that "allowing empty string to be something different from `NULL` would cause a lot of issues." I never understood that argument; it is certainly false, but I would like to at least understand some logical objections. (I have seen many but they really made no sense.) To the contrary, there is a lot of idiotic workarounds we must use precisely because empty string **is** the same as `NULL` in Oracle. The only justification that makes sense for keeping it that way is so as not to render invalid queries that were written before the SQL Standard mandated the correct treatment. –  Apr 05 '18 at 17:47
  • 1
    @WilliamRobertson . . . I could imagine some consistent framework where `''` was not allowed in a `NOT NULL` field. I can imagine none where `= ''` never evaluates to true, because `''` is interpreted as `NULL`. So, if the purpose is to avoid confusion, Oracle has done something much more confusing in my experience. – Gordon Linoff Apr 05 '18 at 17:49
  • 1
    In most domains there's no functional difference between NULL and '', and allowing both to be stored in a single column creates either latent bugs in code that checks whether values are set, or additional complexity in checking if a value is NULL or ''. I'm not saying a DMBS shouldn't allow both, just an application should allow at most one for each column. And NULL is consistent across datatypes, so should be preferred. – David Browne - Microsoft Apr 05 '18 at 18:03
  • Before we get told off for extended comments, I'll just say that missing data is inherently ambiguous however you choose to handle it. Perhaps using null to mean *"cancellation reason unknown"* and `''` for *"definitely no cancellation reason"* solves one technical problem, but print those two records out and show them to the Head of Cancellations and you have another. As someone who has only ever used one kind of nothing, the idea of adding another one fills me with horror despite the occasional inconvenience of `least` and `not in` etc occasionally behaving in counterintuitive ways. – William Robertson Apr 06 '18 at 09:04
  • 1
    Regardless of how we argue proper handling of these, the fact is just about every language and system out there leaves the interpretation and handling of the null versus empty string to the developer as an implementation detail. I'm not surprised Oracle's implementation takes that choice away, and is short sighted enough to assume and coalesce one to the other. – AaronLS Feb 22 '21 at 17:54