SQL Not Null

Last modified on: December 4, 2024

<div class="flex-shrink-0 flex flex-col relative items-end">
<div>
<div class="pt-0">
<div class="gizmo-bot-avatar flex h-8 w-8 items-center justify-center overflow-hidden rounded-full">&nbsp;</div>
</div>
</div>
</div>
<div class="group/conversation-turn relative flex w-full min-w-0 flex-col agent-turn">
<div class="flex-col gap-1 md:gap-3">
<div class="flex max-w-full flex-col flex-grow">
<div class="min-h-8 text-message flex w-full flex-col items-end gap-2 whitespace-normal break-words [.text-message+&amp;]:mt-5" dir="auto" data-message-author-role="assistant" data-message-id="d3075f22-7c0f-46e1-a371-48708d5052c6" data-message-model-slug="gpt-4o-mini">
<div class="flex w-full flex-col gap-1 empty:hidden first:pt-[3px]">
<div class="markdown prose w-full break-words dark:prose-invert light">
<h3><strong>SQL NOT NULL Constraint: A Guide</strong></h3>
<h4><strong>What is the NOT NULL Constraint?</strong></h4>
<p>The <code>NOT NULL</code> constraint is used in SQL to ensure that a column cannot have a <code>NULL</code> value. By applying this constraint to a column, you enforce that every record in the table must contain a valid value for that column. This is particularly useful for columns where it is essential to have a value, such as employee IDs, customer names, or dates of birth.</p>
<hr />
<h3><strong>Basic Syntax</strong></h3>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>CREATE TABLE table_name (
    column_name datatype NOT NULL
);
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li><strong><code>column_name</code></strong>: The name of the column where the <code>NOT NULL</code> constraint is applied.</li>
<li><strong><code>datatype</code></strong>: The data type of the column (e.g., <code>INT</code>, <code>VARCHAR</code>, <code>DATE</code>).</li>
</ul>
<hr />
<h3><strong>Examples</strong></h3>
<h4><strong>1. Creating a Table with a NOT NULL Constraint</strong></h4>
<p>When creating a new table, you can specify that certain columns must have a value by adding the <code>NOT NULL</code> constraint:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    hire_date DATE NOT NULL
);
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li>Here, the <code>employee_name</code> and <code>hire_date</code> columns must always contain a value. The <code>employee_id</code> is the primary key, so it automatically cannot be <code>NULL</code>.</li>
<li>The <code>department</code> column can be <code>NULL</code> (optional).</li>
</ul>
<h4><strong>2. Adding a NOT NULL Constraint to an Existing Column</strong></h4>
<p>You can add a <code>NOT NULL</code> constraint to an existing column using the <code>ALTER TABLE</code> statement:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>ALTER TABLE employees
MODIFY department VARCHAR(50) NOT NULL;
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li>This command modifies the <code>department</code> column to require that it must have a value.</li>
</ul>
<h4><strong>3. Inserting Data into a Table with NOT NULL Constraints</strong></h4>
<p>If you try to insert a row without providing values for columns that have a <code>NOT NULL</code> constraint, an error will be raised:</p>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees (employee_id, employee_name, hire_date)
VALUES (1, 'John Doe', '2024-01-01');
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li>This will work as long as all <code>NOT NULL</code> columns are provided with values. If you omit <code>employee_name</code> or <code>hire_date</code>, the insert will fail.</li>
</ul>
<h4><strong>4. Inserting NULL Values in a NOT NULL Column (Error)</strong></h4>
<div class="contain-inline-size rounded-md border-[0.5px] border-token-border-medium relative bg-token-sidebar-surface-primary dark:bg-gray-950">
<div class="flex items-center text-token-text-secondary px-4 py-2 text-xs font-sans justify-between rounded-t-md h-9 bg-token-sidebar-surface-primary dark:bg-token-main-surface-secondary select-none">sql</div>
<div class="sticky top-9 md:top-[5.75rem]">
<div class="absolute bottom-0 right-2 flex h-9 items-center">
<div class="flex items-center rounded bg-token-sidebar-surface-primary px-2 font-sans text-xs text-token-text-secondary dark:bg-token-main-surface-secondary">
<pre class="language-php"><code>INSERT INTO employees (employee_id, employee_name, hire_date)
VALUES (2, NULL, '2024-02-01');
</code></pre>
</div>
</div>
</div>
</div>
<ul>
<li>This will raise an error because <code>employee_name</code> is a <code>NOT NULL</code> column, and you cannot insert <code>NULL</code> in it.</li>
</ul>
<hr />
<h3><strong>Why Use the NOT NULL Constraint?</strong></h3>
<ol>
<li><strong>Data Integrity:</strong> Ensures that essential columns (such as names or IDs) will always have a valid value.</li>
<li><strong>Enforcing Rules:</strong> Prevents accidental data omissions, especially for critical fields like employee names, addresses, or prices.</li>
<li><strong>Query Performance:</strong> Some databases can optimize queries on tables with <code>NOT NULL</code> columns, as it&rsquo;s known that the column will always contain a value.</li>
<li><strong>Simplifies Data Entry:</strong> Makes sure that data entry for essential fields cannot be skipped.</li>
</ol>
<hr />
<h3><strong>Best Practices</strong></h3>
<ol>
<li><strong>Apply to Key Columns:</strong> Use <code>NOT NULL</code> for columns that are essential for identifying records (e.g., primary keys, employee names).</li>
<li><strong>Avoid on Optional Data:</strong> Don't apply <code>NOT NULL</code> on columns where missing data is acceptable (e.g., a middle name or an optional phone number).</li>
<li><strong>Consider Default Values:</strong> If applicable, combine <code>NOT NULL</code> with a <code>DEFAULT</code> constraint to automatically provide a value when one isn't supplied.</li>
</ol>
<hr />
<h3>&nbsp;</h3>
</div>
</div>
</div>
</div>
</div>
</div>

Web Technology View More