CodeCapsule

SQL NOT NULL constraint

by

Here is a clear, practical, and complete guide to the SQL NOT NULL constraint, including behavior, examples, and engine differences.

✅ What the NOT NULL Constraint Does

NOT NULL ensures that a column cannot contain NULL values.

  • Prevents missing or unknown data

  • Enforces data integrity at the database level

  • Often used for required fields (email, username, timestamps, etc.)

🔹 Basic Syntax

In CREATE TABLE:

CREATE TABLE users (
id INT PRIMARY KEY,
email VARCHAR(255) NOT NULL,
created_at TIMESTAMP NOT NULL
);

In ALTER TABLE:

ALTER TABLE users
MODIFY email VARCHAR(255) NOT NULL; -- MySQL
ALTER TABLE users
ALTER COLUMN email SET NOT NULL; -- PostgreSQL
ALTER TABLE users
ALTER COLUMN email VARCHAR(255) NOT NULL; -- SQL Server

📌 Example: Ensure a required column

CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price DECIMAL(10,2) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP NOT NULL
);

✔ Prevents inserting a product without name or price.

🔥 Inserting Rows With NOT NULL Columns

Valid:

INSERT INTO products (id, name, price)
VALUES (1, 'Laptop', 999.99);

Invalid (missing required field):

INSERT INTO products (id, price)
VALUES (2, 499.99);
-- ERROR: name cannot be null

🔧 Add NOT NULL to an Existing Column (with data present)

PostgreSQL:

UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

ALTER TABLE users
ALTER COLUMN email SET NOT NULL;

MySQL:

UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

ALTER TABLE users
MODIFY email VARCHAR(255) NOT NULL;

SQL Server:

UPDATE users SET email = 'unknown@example.com' WHERE email IS NULL;

ALTER TABLE users
ALTER COLUMN email VARCHAR(255) NOT NULL;

✔ Must remove NULLs first or the ALTER will fail.

🔥 Remove NOT NULL Constraint

PostgreSQL:

ALTER TABLE users
ALTER COLUMN email DROP NOT NULL;

MySQL:

ALTER TABLE users
MODIFY email VARCHAR(255) NULL;

SQL Server:

ALTER TABLE users
ALTER COLUMN email VARCHAR(255) NULL;

🧠 Important Behavior Notes

✔ NOT NULL prevents NULL but not empty strings (''), unless you enforce that separately.
✔ Most engines treat PRIMARY KEY as implicitly NOT NULL.
✔ Columns without an explicit constraint default to NULL unless changed.
✔ For mandatory columns, combine with CHECK or DEFAULT when helpful.

🏎 Best Practices

✔ Apply NOT NULL to all required fields
✔ Add sensible defaults (e.g., DEFAULT CURRENT_TIMESTAMP)
✔ Clean existing NULL data before adding constraints
✔ Avoid allowing NULL on foreign key columns unless logically required
✔ Use migrations to apply changes safely in production

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *