title: Oracle constraint check condition with null and not null
body: <p>My question is about checking constraint, the normal syntax is :</p>
  
  <pre><code>ALTER TABLE barracks
  ADD CONSTRAINT chk_barracks CHECK( status IN('Destroyed', 'constructed'))  
  </code></pre>
  
  <p>This means that column status should be destroyed OR constructed.</p>
  
  <p>In the following I am specifying that barracks doesn't accept null values</p>
  
  <pre><code> ALTER TABLE barracks
   ADD CONSTRAINT chk_barracks CHECK(status IN('Destroyed', 'constructed')
      AND status IS NOT NULL) 
  </code></pre>
  
  <p>Now I added  column <code>colour</code>. I want to specify <b>if</b> <code>barracks</code> is <b>constructed</b>, then <code>colour</code> should be <b>RED</b> only and the column <b>STATUS</b> should be <B>NOT NULL</B></p>
  
  <p>If BARRACKS IS <B>DESTROYED</B></p>
  
  <p>Then <code>colour</code> should be <B>BLACK</B> and the column <b>STATUS</b> should be <b>NULL</b>.</p>
  
  <p>So here is the checking constraint that I wrote:</p>
  
  <pre><code> ALTER TABLE barracks
   ADD CONSTRAINT chk_barracks CHECK( ((status IN ('Destroyed', 'constructed')
           AND status IS NOT NULL) 
           AND (color IN('RED') )) 
      OR (( status IN('Destroyed', 'constructed') 
           AND status IS NULL) 
           AND (color IN('BLACK') )))  
  </code></pre>
  
  <p>The problem that if color is RED, I am still able to insert <code>NULL</code> into the column <code>status</code>. How to solve this constraint in a way it will check my conditions that I have mentioned above?</p>