🚫 MySQL Constraints Overview – NOT NULL, DEFAULT, CHECK

---

🧲 Introduction – Why Use Constraints?

MySQL constraints are rules enforced at the database level to ensure data quality, validity, and reliability. Constraints like NOT NULL, DEFAULT, and CHECK help prevent:

• Missing or invalid values
• Inconsistent data entries
• Incorrect logic in your applications

By defining constraints in your schema, you offload responsibility from application logic and ensure database-level integrity.

🎯 In this guide, you’ll learn:

• How to use NOT NULL, DEFAULT, and CHECK constraints
• Their syntax and behavior in MySQL 8+
• Practical examples and best practices

---

❗ 1. NOT NULL – Disallow Missing Values

🔹 Syntax

column_name data_type NOT NULL

🔹 Example

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  email VARCHAR(100)
);

Explanation:

• username is required — attempts to insert NULL will fail.
• Enforces presence of critical values like names, passwords, etc.

---

💡 2. DEFAULT – Provide a Fallback Value

🔹 Syntax

column_name data_type DEFAULT default_value

🔹 Example

CREATE TABLE tasks (
  task_id INT PRIMARY KEY,
  status VARCHAR(20) DEFAULT 'pending',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Explanation:

• status: Defaults to 'pending' if not specified.
• created_at: Automatically records insert time.

✅ Useful for audit columns, flags, and optional fields.

---

✅ 3. CHECK – Validate Conditions (MySQL 8+)

🔹 Syntax

CHECK (expression)

🔹 Column-Level Example

CREATE TABLE products (
  id INT PRIMARY KEY,
  price DECIMAL(10,2) CHECK (price >= 0)
);

Explanation:

• Ensures price is not negative.
• Inserts or updates violating the condition will fail.

🔹 Table-Level Example

CREATE TABLE accounts (
  id INT,
  balance DECIMAL(10,2),
  CHECK (balance <= 100000)
);

Note:
MySQL 5.x parses but ignores CHECK. Enforced only from MySQL 8.0.16+.

---

📋 Constraint Comparison Table

Constraint	Purpose	Enforced In	Example Use Case
NOT NULL	Disallow missing values	All MySQL versions	Required fields (username)
DEFAULT	Provide fallback value	All MySQL versions	Status flags, timestamps
CHECK	Enforce conditional rules	MySQL 8.0.16+	Salary ≥ 0, Age ≤ 120

---

🧪 Add Constraints to Existing Tables

🔹 Add NOT NULL

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

🔹 Add DEFAULT

ALTER TABLE tasks ALTER COLUMN status SET DEFAULT 'pending';

🔹 Add CHECK

ALTER TABLE products ADD CHECK (stock >= 0);

---

❌ Constraint Violations – When Do Errors Occur?

Constraint	Violation Example	Result
NOT NULL	Insert with NULL username	Error
DEFAULT	None provided (uses default)	No error
CHECK	Insert with price = -10	Error (if CHECK enforced)

---

📘 Best Practices

✅ Tip	💡 Why It Matters
Always use NOT NULL for required fields	Avoids null reference issues in app logic
Set DEFAULT for non-critical fields	Simplifies insert queries
Use CHECK for critical logic conditions	Keeps logic centralized in schema
Validate CHECK support in version	Avoid silent failures in old MySQL versions
Name constraints explicitly (optional)	Easier to reference when dropping or altering

---

🚀 Real-World Use Cases

Use Case	Constraint	Why
Ensure status is always set	DEFAULT	Defaults to 'pending'
Prevent negative inventory	CHECK	stock >= 0 condition
Force email entry	NOT NULL	Avoids incomplete user records
Audit timestamps	DEFAULT TIMESTAMP	Automates logging without manual input

---

📌 Summary – Recap & Next Steps

Constraints like NOT NULL, DEFAULT, and CHECK help you design smart, self-validating schemas. They ensure required fields exist, fallbacks are applied, and invalid data is rejected at the source.

🔍 Key Takeaways

• Use NOT NULL to enforce required fields
• Use DEFAULT to simplify inserts and set fallback logic
• Use CHECK to define conditional business rules
• CHECK is enforced only in MySQL 8.0.16+

⚙️ Real-World Relevance

These constraints are essential for CRM, billing, e-commerce, ERP, HR systems, and more where data rules must be enforced automatically.

---

❓ FAQ – MySQL Constraint Overview

---

❓ Is CHECK enforced in all MySQL versions?

❌ No. It’s only enforced in MySQL 8.0.16+.

---

❓ Can I have both DEFAULT and NOT NULL?

✅ Yes, and it’s recommended:

status VARCHAR(20) NOT NULL DEFAULT 'active'

---

❓ What happens if I insert nothing into a DEFAULT column?

The default value will be used automatically.

---

❓ Can I remove a CHECK constraint?

✅ Yes:

ALTER TABLE table_name DROP CHECK check_name;

You’ll need the constraint name.

---

❓ Can I use expressions in DEFAULT?

✅ Yes (e.g., CURRENT_TIMESTAMP),
❌ But not complex expressions like functions or subqueries.

---

Share Now :