Frank's Java Development Blog

I came across some code the other day that simply horrified me. I was looking at a table that had a field name which implied it stored a boolean value (e.g. IS_LAUGHABLE). The field type is defined as a char(1), allows NULL values and defined no default value. Looking at the data in the column I found a combination of the following values: T, F, Y, and N.

I didn’t know what to do. I wanted to insert a new row but could not decide which value is correct to denote for boolean true or false? Then I thought how would the code handle these values? The above value combinations make it impossible to use the ternary operator to retrieve the desired boolean value such as:

boolean isLaughable =
    resultSet.getString("is_laughable").equals("Y")?true:false);

Then I found that someone had gone through the trouble of creating a utility method to convert the various character values to the desired boolean output of true or false. The method looked something like this:

static public boolean getBooleanFromString(String attr)
{
  if ( attr == null ) {
		return false ;
  }
  boolean val = false;

  if ("true".equalsIgnoreCase(attr)
         || "t".equalsIgnoreCase(attr)
         || "y".equalsIgnoreCase(attr)
         || "yes".equalsIgnoreCase(attr))
  {
		val = true;
  }

  return val;
}

I couldn’t believe it! Instead of cleansing the data they just applied a Band-Aid to get around the issue. This is not the proper way to store this type of data. Possible values for this field can only be one of two values; true or false. Physically what characters represent those values is up to the developer. However since it represents a boolean value it should not allow more than two values to be inserted nor should it allow NULL values.

The proper action to take here is to clean up the data and add a check constraint to the table to restrict the data stored in this field. This can be done with two simple ALTER statements:

ALTER TABLE jokes
ADD IS_LAUGHABLE char(1) DEFAULT 'N' NOT NULL; 

ALTER TABLE jokes
ADD CONSTRAINT jokes_is_laughable_ck
CHECK (IS_LAUGHABLE IN('Y','N'));

Adding check constraints to your database will greatly increase data integrity, reduce data related bugs and make programming easier since you know exactly what values to expect from the database. Any situation where you have a finite list of values such as boolean values, or state (e.g. Error, Success, Processing, etc) are good candidates for value check constraints. Use good judgment and leverage check constraints to your advantage.

No related posts.