Mastering NOT NULL Constraints in SQL: A Complete Guide

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value.

One important point to note about this constraint is that it cannot be defined at table level.

Example using NOT NULL constraint

Code:

<span class="pln">
CREATE TABLE </span><span class="typ">Student</span><span class="pun">(</span><span class="pln">
s_id </span><span class="kwd">int</span><span class="pln"> NOT NULL</span><span class="pun">,</span><span class="pln">
 </span><span class="typ">Name</span><span class="pln"> varchar</span><span class="pun">(</span><span class="lit">60</span><span class="pun">),</span><span class="pln">
 </span><span class="typ">Age</span><span class="pln"> </span><span class="kwd">int</span><span class="pln">
</span><span class="pun">);</span><span class="pln">
</span>

The above query will declare that the s_id field of Student table will not take NULL value.

SQL NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "Student" table is already created, use the following SQL:

Code:

<span class="pln">
ALTER TABLE </span><span class="typ">Student</span><span class="pln">
MODIFY </span><span class="typ">Age</span><span class="pln"> </span><span class="kwd">int</span><span class="pln"> NOT NULL</span><span class="pun">;</span><span class="pln">
</span>