Boolean Field in Oracle

Question

Yesterday I wanted to add a boolean field to an Oracle table   However  there isn t actually a boolean data type in Oracle   Does anyone here know the best way to simulate a boolean   Googling the subject discovered several approaches   Use an integer and just don t bother assigning anything other than 0 or 1 to it  Use a char field with  Y  or  N  as the only two values  Use an enum with the CHECK constraint    Do experienced Oracle developers know which approach is preferred canonical

User · Accepted Answer

I found this link useful   Here is the paragraph highlighting some of the pros cons of each approach      The most commonly seen design is to imitate the many Boolean-like   flags that Oracle s data dictionary views use  selecting  Y  for true   and  N  for false  However  to interact correctly with host   environments  such as JDBC  OCCI  and other programming environments    it s better to select 0 for false and 1 for true so it can work   correctly with the getBoolean and setBoolean functions    Basically they advocate method number 2  for efficiency s sake  using    values of 0 1  because of interoperability with JDBC s getBoolean   etc   with a check constraint a type of CHAR  because it uses less space than NUMBER      Their example    create table tbool  bool char check  bool in  0 1    insert into tbool values 0   insert into tbool values 1

User · Answer

In our databases we use an enum that ensures we pass it either TRUE or FALSE   If you do it either of the first two ways it is too easy to either start adding new meaning to the integer without going through a proper design  or ending up with that char field having Y  y  N  n  T  t  F  f values and having to remember which section of code uses which table and which version of true it is using

User · Answer

The database I did most of my work on used  Y     N  as booleans  With that implementation  you can pull off some tricks like    Count rows that are true  SELECT SUM CASE WHEN BOOLEAN FLAG    Y  THEN 1 ELSE 0  FROM X   When grouping rows  enforce  If one row is true  then all are true  logic  SELECT MAX BOOLEAN FLAG  FROM Y Conversely  use MIN to force the grouping false if one row is false

User · Answer

The best option is 0 and 1  as numbers - another answer suggests 0 and 1 as CHAR for space-efficiency but that s a bit too twisted for me   using NOT NULL and a check constraint to limit contents to those values   If you need the column to be nullable  then it s not a boolean you re dealing with but an enumeration with three values      Advantages of 0 1    Language independent   Y  and  N  would be fine if everyone used it  But they don t  In France they use  O  and  N   I have seen this with my own eyes   I haven t programmed in Finland to see whether they use  E  and  K  there - no doubt they re smarter than that  but you can t be sure  Congruent with practice in widely-used programming languages  C  C    Perl  Javascript  Plays better with the application layer e g  Hibernate Leads to more succinct SQL  for example  to find out how many bananas are ready to eat select sum is ripe  from bananas instead of select count    from bananas where is ripe    Y  or even  yuk  select sum case is ripe when  Y  then 1 else 0  from bananas   Advantages of  Y   N     Takes up less space than 0 1 It s what Oracle suggests  so might be what some people are more used to   Another poster suggested  Y  null for performance gains  If you ve proven that you need the performance  then fair enough  but otherwise avoid since it makes querying less natural  some column is null instead of some column   0  and in a left join you ll conflate falseness with nonexistent records

User · Answer

A working example to implement the accepted answer by adding a  Boolean  column to an existing table in an oracle database  using number type    ALTER TABLE my table name ADD   my new boolean column number 1  DEFAULT 0 NOT NULL CONSTRAINT my new boolean column CHECK  my new boolean column in  1 0        This creates a new column in my table name called my new boolean column with default values of 0  The column will not accept NULL values and restricts the accepted values to either 0 or 1

User · Answer

To use the least amount of space you should use a CHAR field constrained to  Y  or  N    Oracle doesn t support BOOLEAN  BIT  or TINYINT data types  so CHAR s one byte is as small as you can get

User · Answer

Either 1 0 or Y N with a check constraint on it  ether way is fine  I personally prefer 1 0 as I do alot of work in perl  and it makes it really easy to do perl Boolean operations on database fields   If you want a really in depth discussion of this question with one of Oracles head honchos  check out what Tom Kyte has to say about this Here

User · Answer

Oracle itself uses Y N for Boolean values   For completeness it should be noted that pl sql has a boolean type  it is only tables that do not     If you are using the field to indicate whether the record needs to be processed or not you might consider using Y and NULL as the values   This makes for a very small  read fast  index that takes very little space

[oracle] Boolean Field in Oracle

Examples related to oracle

Examples related to boolean

Examples related to sqldatatypes