Sunday, June 24, 2007

Oracle bogosity - no boolean column type

Oracle has no boolean column type. That is, in a create table statement, you can't say:
MY_BOOLEAN_COLUMN boolean
I've seen some discussion of this and most of it boils down to what Tom Kyte says, that you don't need a boolean type since you can use an integer(1/0), a character(T/F,Y/N,even 1/0) or anything you like. I suppose you could use varchar(5) and use "true" and "false".

For a while I accepted this explanation, until I ran across an oracle database that had boolean types using all of these conventions (in different tables, although I did see T/F and Y/N for different columns in the same table!).

Of course it was implementer error that they didn't use check constraints on those pseudo-boolean fields. So in the integer field, it was possible to set the "boolean" to 314. and in the character fields, well, it was possible to have T/F/Y/N/1/0 all in the same column (fortunately, the character field was limited to one character, else I would have had even more fun with that stupidity).

I suppose Oracle is that way (bogus) because of legacy code, compatibility issues, and inertia. I can see internal discussions going sort of in the direction of creating a boolean type but stopping short of actually doing so because it just seems so hard, or no one wants to rock the boat and introduce potential compatibility issues (e.g., when oracle software uses boolean, and then won't work with oracle installations which didn't have the boolean type).

Another problem, of course, is that anyone confused enough to use all those different encodings for pseudo-booleans, and worse, IN THE SAME COLUMN with no check constraints, is going to continue being confused when the boolean type is introduced. They won't use the boolean type, they'll continue using their iodine-deficiency-generated solution. But just because idiots will continue in their idiocy is no reason to avoid doing the right thing. As it is, when a boolean field is concerned, there's going to be a discussion as to which convention to use and then there's going to be a random choice (unless the shop in question has advanced to the point of having a convention for boolean fields, in which case I say, bravo). If a true boolean column type were available, the canonical answer would be to use that. Iodine-deficient DBAs could still use their non-boolean hacks, but at least a canonically correct solution would exist, guiding iodine-sufficient (but trained by incompetent or iodine-deficient DBAs) in the right direction.

No comments: