Fresh New Horror: PK on a Float Data Type

Here's a new thing to be scared about in the night, as if you weren't already terrified of floats after my previous two posts about the ills of the approximate-number data types.

Creating a primary key on a float data type can lead to what appear to be duplicate primary key values. Attempting to convert these values from float to an integer data type results in a primary key violation.

In fact, that's how we found it at the client site. Having already made the discovery that floats were to blame for randomly-appearing approximation problem that surfaced as business rule issues, we set about altering the column's data type to decimal (19,1). The error we received was "Violation of PRIMARY KEY constraint 'PK_invoicehdr'...".

Here's the grisly proof of good, well-intentioned people doing very bad things to an innocent database.

Parental Guidance: May contain some disturbing images. Viewer discretion advised.

Exhibits 1 and 2 above, we have the primary key declared on the invno column, declared as a float data type. (If it wasn't already very clear, this is a really bad idea.)

You ready for it?

There is nothing wrong with your television set. Do not attempt to adjust the picture. This is very real.

Pretty bad, right? Remember, this is their PRIMARY KEY.

Let's recreate the problem by manually inserting data that would conflict, or rather, would not conflict.

Turns out, in a table with millions of rows, we found only three instances where this was happening. I'm shocked that there weren't more - Murphy's Law ensured it happened, but it should have led to more than three times out of millions. Still, pretty bad.

Not surprisingly, this has lead to a pretty significant business rules problem.

Still working out the details of the fix to the database to recode those numbers before we try to convert the data types to decimal. And still working on changes to the offending .NET code (also probably using float data types).