<p>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</p>

<ol>
<li><p>Use an integer and just don't bother assigning anything other than 0 or 1 to it.</p></li>
<li><p>Use a char field with 'Y' or 'N' as the only two values.</p></li>
<li><p>Use an enum with the CHECK constraint.</p></li>
</ol>

<p>Do experienced Oracle developers know which approach is preferred/canonical?</p>

[{'answer_id': 30069, 'author': 'Ryan Ahearn', 'author_id': 75, 'author_profile': 'https://Stackoverflow.com/users/75', 'pm_score': 1, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 30072, 'author': 'ColinYounger', 'author_id': 1223, 'author_profile': 'https://Stackoverflow.com/users/1223', 'pm_score': 7, 'selected': True, 'text': '<p>I found <a href="http://www.techrepublic.com/article/oracle-tip-choosing-an-efficient-design-for-boolean-column-values/" rel="noreferrer">this</a> link useful.</p>\n\n<p>Here is the paragraph highlighting some of the pros/cons of each approach.</p>\n\n<blockquote>\n  <p>The most commonly seen design is to imitate the many Boolean-like\n  flags that Oracle\'s data dictionary views use, selecting \'Y\' for true\n  and \'N\' for false. However, to interact correctly with host\n  environments, such as JDBC, OCCI, and other programming environments,\n  it\'s better to select 0 for false and 1 for true so it can work\n  correctly with the getBoolean and setBoolean functions.</p>\n</blockquote>\n\n<p>Basically they advocate method number 2, for efficiency\'s sake, using </p>\n\n<ul>\n<li><em>values</em> of 0/1 (because of interoperability with JDBC\'s <code>getBoolean()</code> etc.) with a check constraint</li>\n<li>a <em>type</em> of CHAR (because it uses less space than NUMBER). </li>\n</ul>\n\n<p>Their example:</p>\n\n<blockquote>\n<pre><code>create table tbool (bool char check (bool in (0,1));\ninsert into tbool values(0);\ninsert into tbool values(1);`\n</code></pre>\n</blockquote>\n'}, {'answer_id': 30091, 'author': 'Bill the Lizard', 'author_id': 1288, 'author_profile': 'https://Stackoverflow.com/users/1288', 'pm_score': 5, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 30120, 'author': 'Erick B', 'author_id': 1373, 'author_profile': 'https://Stackoverflow.com/users/1373', 'pm_score': 2, 'selected': False, 'text': '<p>The database I did most of my work on used \'Y\' / \'N\' as booleans. With that implementation, you can pull off some tricks like:</p>\n\n<ol>\n<li><p>Count rows that are true:<br>\nSELECT SUM(CASE WHEN BOOLEAN_FLAG = \'Y\' THEN 1 ELSE 0) FROM X  </p></li>\n<li><p>When grouping rows, enforce "If one row is true, then all are true" logic:<br>\nSELECT MAX(BOOLEAN_FLAG) FROM Y<br>\nConversely, use MIN to force the grouping false if one row is false.</p></li>\n</ol>\n'}, {'answer_id': 36973, 'author': 'Matthew Watson', 'author_id': 3839, 'author_profile': 'https://Stackoverflow.com/users/3839', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n\n<p>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 <a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:6263249199595" rel="noreferrer">Here</a></p>\n'}, {'answer_id': 223139, 'author': 'Leigh Riffel', 'author_id': 27010, 'author_profile': 'https://Stackoverflow.com/users/27010', 'pm_score': 5, 'selected': False, 'text': '<p>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.  </p>\n\n<p>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.</p>\n'}, {'answer_id': 16398732, 'author': 'Andrew Spencer', 'author_id': 587365, 'author_profile': 'https://Stackoverflow.com/users/587365', 'pm_score': 4, 'selected': False, 'text': "<p>The best option is 0 and 1 (as numbers - another answer suggests 0 and 1 as <em>CHAR</em> 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...)</p>\n\n<p>Advantages of 0/1:</p>\n\n<ul>\n<li>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.</li>\n<li>Congruent with practice in widely-used programming languages (C, C++, Perl, Javascript)</li>\n<li>Plays better with the application layer e.g. Hibernate</li>\n<li>Leads to more succinct SQL, for example, to find out how many bananas are ready to eat <code>select sum(is_ripe) from bananas</code> instead of <code>select count(*) from bananas where is_ripe = 'Y'</code> or even (yuk) <code>select sum(case is_ripe when 'Y' then 1 else 0) from bananas</code></li>\n</ul>\n\n<p>Advantages of 'Y'/'N':</p>\n\n<ul>\n<li>Takes up less space than 0/1</li>\n<li>It's what Oracle suggests, so might be what some people are more used to</li>\n</ul>\n\n<p>Another poster suggested 'Y'/null for performance gains. If you've <em>proven</em> that you need the performance, then fair enough, but otherwise avoid since it makes querying less natural (<code>some_column is null</code> instead of <code>some_column = 0</code>) and in a left join you'll conflate falseness with nonexistent records.</p>\n"}, {'answer_id': 39841035, 'author': 'Ben.12', 'author_id': 2209991, 'author_profile': 'https://Stackoverflow.com/users/2209991', 'pm_score': 2, 'selected': False, 'text': '<p>A working example to implement the accepted answer by adding a "Boolean" column to an existing table in an oracle database (using <code>number</code> type):</p>\n\n<pre><code>ALTER TABLE my_table_name ADD (\nmy_new_boolean_column number(1) DEFAULT 0 NOT NULL\nCONSTRAINT my_new_boolean_column CHECK (my_new_boolean_column in (1,0))\n);\n</code></pre>\n\n<p>This creates a new column in <code>my_table_name</code> called <code>my_new_boolean_column</code> with default values of 0. The column will not accept <code>NULL</code> values and restricts the accepted values to either <code>0</code> or <code>1</code>.</p>\n'}]
['https://Stackoverflow.com/questions/30062', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1694/']