Our company is interfacing with another software company for a joint project, and we were told that, if a particular value should not be displayed, we should pass in a -5000 (their arbitrary sentinel value); the reason is that no number column in their Oracle database supports null values, on the recommendation of their (now former) Oracle dev. This company also writes the vast majority of their code in VB6 (slowly transitioning to VB.NET, which is another topic for another day...). Out of pure curiosity, is there any valid reason for this recommendation? I can't think of any on my side.

--- edit

Thanks for the feedback all. I posed the same question on CodeProject.com (link) and received very similar feedback. It appears the only time one could possibly begin to justify this practice is related to foreign keys, and I can state that they use no foreign keys anywhere in the system. The developer that made this determination (I used to work at that company) has significantly more experience than I, so I wanted to make sure there wasn't a valid reason for this before derision ensues.

  • 2
    You mean, other than "that is what their API specifies"? – Robert Harvey Aug 22 '12 at 19:16
  • Yes, I'm more curious about why their API would specify that in the first place; is there a reason for this practice, or is this just some lunacy? – IndifferentDisdain Aug 22 '12 at 19:17
  • 3
    Lunacy of the highest order! – Philᵀᴹ Aug 22 '12 at 19:40
  • 1
    Related question on SO: Standard use of 'Z' instead of NULL to represent missing data? – Andriy M Aug 22 '12 at 23:23

Realistically, the requirement is crazy. Like all great crazy ideas, however, it is probably based on a nugget of potential reasonableness taken far out of context by people that have no understanding of the underlying rationale.

It can be reasonable to design a database schema such that no NULL values are allowed. If you do that, however, you are committing to a level of normalization where every non-required element is broken out into an separate table with an appropriate foreign-key reference back to the parent. It's not often done in practice but in cases where it makes sense to do, there can be benefits.

If you are going to design a database schema such that no NULL values are allowed, it makes no sense to allow let alone require magic values to indicate that something is unknown. That introduces all the problems that allowing NULL values has plus it adds additional code to check for the magic values that has to get repeated all over the place. It makes no sense to develop an API that requires magic values to be passed in regardless of the database design-- if you're going to hobble your code with checks for magic values, you really ought not let that insanity propagate out to other systems.

  • +1 and the additional code to check for the magic values cannot use well known functions like COALESCE() - so it becomes even more complicated. – ypercubeᵀᴹ Aug 23 '12 at 12:33
  • And the values need to be stored in any index on that column. Indexes don't have to store null values. – Tripp Kinetics Oct 24 '14 at 14:45

There is No Valid Reason to use a magic value instead of NULL. This might be the thought process of someone creating this mess. They write something like this:

 SELECT c1, c2 FROM t1 WHERE c3 < 30;

When this doesn't return the results they are expecting, they realize that it does not include NULLs and would need to write this:

SELECT c1, c2 FROM t1 WHERE c3 < 30 OR c3 IS NULL;

They don't want to write or forget in the future to write this, so they come up with the solution of making all NULLS -5000. Magically their original query handles NULLs without any changes. What they don't realize is that now someone who wants to exclude these values has to write this:

SELECT c1, c2 FROM t1 WHERE c3 < 30 AND c3 <> -5000;

Or if they wanted these values and are searching a higher range:

SELECT c1, c2 FROM t1 WHERE c3 > 40 OR c3 = -5000;

They also may not realize that the following would no longer be meaningful:


Instead a person has to remember the magic value. With each datatype used they have to remember more magic values e.g. 1/1//1900, "Z", -5000. Furthermore, when the magic value is in the data they must also remember alternate magic values.

So, for one specific case it makes the code simpler at the expense of other cases, not to mention disk space, index size, query parsing, consistency, etc.


It's utter madness and there's no justification for it. NULL was created to represent the absence of a value & to use an actual value like -5000 is bonkers.

Ordinarily I wouldn't write an answer this short, but the question deserves to be one of the most visible on dba.se & the more answers the better.


I thought about this for a bit trying to be positive and justify the need for using an arbitrary value instead of a null and there seems (to me at least) to be no valid reason for this, except perhaps in a closed data-mining dataset to improve and simplify performance and queries, and then only in cases where the numbers aren't values that might skew the data. Even this would have to be considered carefully. In all real world situations giving a value to null is not good practice. This turns a NOT NULL column definition from your friend to your enemy since it really is not true.

It is a very different thing to say that our application should not accept a NULL value for some (or even all) columns. This is sensible and good practice and there are well documented benefits to not allowing nulls (keys and indexes and statistical calculations for example). However, assigning a value to "sit in the place" of a null is not at all the same. It is the rod for your own back, since you have to first select a value that will never ever be used, filter out this value as you would the null and remember not to use it in calculations and summaries and remove it from external data feeds. This is at least as bad a using a null to represent an actual value, which is what you tell yourself you are avoiding, but you are not.

Most issues that nulls cause, once understood, can be dealt with (better normalisation, function based or bitmap indexes or with a simple WHERE x IS NOT NULL). Do you think that at some large Telco or at Amazon in the monthly performance meeting some DBA is outlining this great plan to speed up queries on their enormous datasets a bit "by replacing null with an arbitrary value, something like -5000, or whatever - I'm open on the value...". Or do you think they spend their time split between better application design to filter out unwanted nulls and query optimisation based on the actual data they get given? OK, fine maybe a monthly meeting is a bit optimistic, but whenever they happen I can assure your that "Replacing nulls with -5000 (or whatever) for a better API" is not an agenda item.

For me it is fine to say that I won't accept missing data (you must have an age or a price or a region code or whatever) and sometimes even fine to say for this column there is a default value that will be entered if you don't put something else. It is not fine to set aside a value to mean null. Think about middle-name fields as an example. Sometimes these will not exist since parents are too lazy to fill in all boxes. Do we add "none" or "missing" or "unknown" to our data to improve our searches? No because there may be strange people that change their names to these values and so when we print out the data we don't know if we must include it or not. It is a simple, but far reaching example. We know about NULL and have predictable built in functions to deal with it. You can not code this any better.

If no answer (or NULL) is not a valid response to your input request then don't allow it in the application or in the database, if it a good response then you must allow it in both your application and your database and deal with it as a valid response. If it is part of a set of valid responses your database must be designed to store it. After all you don't say hey, number fields are so boring lets store numbers in blobs and use pictures of wild animals to represent each number, because that's nuts (cool but nuts). We also don't decide that we don't like the letter B, and like some cruel Sesame Street nightmare replace it with a # in our data. If B isn't a response we want we tell the user "Hey, you can't put a B here". So why treat null differently?

So avoid nulls you don't want at the application level and deal with them in you database where you do accept them otherwise as sure as giraffe + giraffe = hippo your pointless data wrangling will get you in trouble.

  • 2
    My parents were not lazy and I have no middle name by the way. Not all people live in the USA. – ypercubeᵀᴹ Sep 7 '12 at 14:35
  • 1
    It was meant to be a light-hearted example, no offence meant. There are, of course, many people with no middle names (the first point) for many quite valid reasons (the main point). Null in this column tells you nothing about why it was missing. Not sure about your geo-political angle - I don't live in the USA but do in fact have a middle name. It's difficult to make assumptions based on missing data I guess. – user3629 Sep 7 '12 at 14:52
  • No offense taken. I upvoted your answer actually. I think you hit the nail with your main point that there is difference between not accepting/allowing Nulls in the database and replacing Nulls with a magic value. – ypercubeᵀᴹ Sep 7 '12 at 16:50
  • 5
    I'd love it if my middle name was "-5000"! :D – Philᵀᴹ Sep 7 '12 at 17:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy