Lessons Learnt This Week…

I still surprise myself sometimes when I come across a bit of weird behaviour in code, or some strange anomaly which I’m attempting to debug, when I find out the root cause and the answer, I only then think “Oh, hang on a moment, I knew that!”

I had two such moments this week:

  1. Never create a Unique Index over nullable columns in a database – particularly MySql/Postgres. It won’t function as you might expect it to. You could potentially end up with duplicates because the underlying database engine doesn’t regard nulls as actual values, so thereby essentially ignores them in the unique index. Consider the following table, with a Unique Index over all the columns:

    These would be allowed values, even though you’re expecting NULL‘s to be included into the unique index. Beware of this kind of assumption – it will bite you later. If you’d like to know more re MySql, then head over here, otherwise, here is the snippet off of their manual:

    A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

  2. Never expect BigDecimal.ZERO.equals(anotherBigDecimal) to return true, even if you’re comparing 0 with 0.00. The reason being is that the equals method takes into consideration the scale of the number you are trying to compare. So even 0 is NOT equal to 0.00! In cases where the scale size might be different, but the actual value itself