Oracle, Nulls and the empty string

Oracle has an unusual feature, which attracts it a lot of criticism. If you try to insert the empty string into a column marked "not null", you get an error. The empty string is treated the same as "null" by Oracle.

This is different to programming languages (and indeed other databases, at least MySQL), which means one has to be careful not to make a mistake when using a programming language to talk to the database. That it's different from other systems is the main reason for the criticism.

However, how many times have you wanted the following to be allowed in your data schema, for example on a "first name" column:

• Writing nulls is not allowed
• Writing the empty string is allowed

I just wrote a program and the front-end framework helpfully noticed that the field was "not null" in the database and gave the user an error in the front-end if the field was empty. However when I altered the code slightly, it no longer gave an error. Because the field in the program was the empty string, and not null.

However, I assert, when dealing with data, checking only for not null is not useful; you also want to check that the string contains some data in that case.

I have now updated the framework, so that if the field is marked "not null", then the error is presented to the front-end not only if the variable in the program is "null", but also if it is the empty string.

(Note: I am not advocating e.g. Java lose the distinction between x==null and x.isEmpty(): for some reason this is a useful distinction when doing programming and data manipulation—such as null indicating that a variable isn't initialized yet—I just don't think it's a useful distinction in a system solely designed to model persistent data.)