Simulate a Boolean Data Type in a Database Table

I want to create a column in an SQL table and only allow two values. They could be 1 or 0, Y or N, or whatever. I don’t want the database to allow any other values into the column. I want the default to be the “false” value. How would you define such a column in SQL? I understand that Oracle supports a BOOLEAN data type, but I don’t think such exists on the AS/400.

—Guru Reader

You are correct, SQL Server has the BIT data type and Oracle has the BOOLEAN data type but there is no equivalent data type in DB2 for i. However, you can use a CHECK constraint to simulate a BOOLEAN type. Here’s an example:

Create Table TestBoolean
  (Bool Numeric(1,0) Not Null Default 0 
  Constraint BoolCheck Check (Bool In (0,1)))

In this case, the CHECK constraint mandates the value of column Bool to contain a 0 or 1 (identical to the behavior of SQL Server’s BIT data type). Of course DEFAULT will give your column a default value of 0 if one isn’t explicitly given. Keep in mind that a constraint name must be unique within a schema. Therefore you won’t be able to specify BoolCheck as a constraint name on more than one table within the same schema (library).

You can also emulate a BOOLEAN value with a character field that contains the values ‘Y’ and ‘N’, such as:

Create Table TestBoolean
  (Bool Char(1) CCSID 37 Not Null Default 'N'
  Constraint BoolCheck Check (Bool In ('Y', 'N')))

Now, when you insert or update a value into this column that is not allowed by the check constraint you get the following error:

SQL0545 INSERT or UPDATE not allowed by CHECK constraint.

If you have multiple fields with the same CHECK requirements, you can implement the validation for all three columns within one constraint:

Create Table TestBoolean
  (Bool1 Numeric(1,0) Not Null Default 0, 
   Bool2 Numeric(1,0) Not Null Default 0, 
   Bool3 Numeric(1,0) Not Null Default 0, 
  Constraint CheckFlags Check(Bool1 In (1,0) And
                      Bool2 In (1,0) And Bool3 In (1,0)))

Of course you can vary this example to allow NULLs if required.

Finally, for compatibility with other database servers, one other possible variation is to create a distinct type called BIT. (You can’t use the name BOOLEAN because it is an SQL reserved keyword–maybe IBM intends to give us a BOOLEAN data type in the future.) However, when implementing the distinct type you will still need to specify the CHECK constraint at the table level to enforce the allowed values. Also, distinct types require more processing overhead. See the documentation for the CREATE TYPE command for more info.

–Mike

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Send your questions or comments for Michael to Ted Holt via the IT Jungle Contact page.

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot