Hibernate/Database Boolean data types

A constraint and a decode is sufficient to make a native Boolean data type redundant. Pity since it means that Boolean fields are frequently used and because of the lack of a standard they are not portable across databases, ORMs, etc.

Hibernate –v- DBMS

MySQL

tinyint(1)

DB2

smallint

SQL Server

bit

Oracle

number(1,0)

 

All should disallow nulls.

default not null

All should contrain the set of input values.

check (field = 1 or field = 0)

Db2=>

db2 => alter table testbool add flag smallint check (flag=1 or flag=0) default not null

DB20000I The SQL command completed successfully.

db2 => describe table testbool

 


Data type Column

Column name schema Data type name Length Scale Nulls

——————————- ——— ——————- ———- —– ——

COL SYSIBM INTEGER 4 0 Yes

FLAG SYSIBM SMALLINT 2 0 No

 

2 record(s) selected.

 

db2 => insert into testbool values (1,2)

DB21034E The command was processed as an SQL statement because it was not a

valid Command Line Processor command. During SQL processing it returned:

SQL0545N The requested operation is not allowed because a row does not

satisfy the check constraint “TESTBOOL.SQL160718122408140”.

SQLSTATE=23513

db2 =>

 

References

https://technet.microsoft.com/en-us/library/ms188258(v=sql.105).aspx

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/apsg/src/tpc/db2z_checkconstraintenforcement.html