Using Constraints in SQL: NOT NULL, UNIQUE, CHECK, and DEFAULT

---

Table of Contents

1. Introduction
2. What Are Constraints in SQL?
3. Why Use Constraints?
4. NOT NULL Constraint
5. UNIQUE Constraint
6. CHECK Constraint
7. DEFAULT Constraint
8. Combining Multiple Constraints
9. Adding Constraints to Existing Tables
10. Dropping Constraints
11. Naming Constraints for Clarity
12. Column-Level vs Table-Level Constraints
13. Real-World Example: Enforcing Data Validity
14. Constraint Violation Errors
15. Performance Impact of Constraints
16. Difference Between UNIQUE and PRIMARY KEY
17. Best Practices for Using Constraints
18. Using Constraints in Insert Statements
19. Compatibility in Different SQL Dialects
20. Summary and What’s Next

---

1. Introduction

Constraints in SQL are rules applied to columns to enforce the correctness, validity, and integrity of the data stored in tables. They prevent bad data from entering your database and reduce the need for application-level validations.

---

2. What Are Constraints in SQL?

A constraint is a rule that restricts the values that can be stored in a column or set of columns. They are enforced by the database engine automatically.

---

3. Why Use Constraints?

• Prevent invalid or inconsistent data
• Maintain relational integrity
• Reduce bugs caused by bad input
• Ensure business logic enforcement at the database level
• Improve confidence in data quality

---

4. NOT NULL Constraint

The NOT NULL constraint ensures that a column must always have a value — it cannot be NULL.

sqlCopyEditCREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

If you try to insert a row without a name, it will throw an error.

---

5. UNIQUE Constraint

The UNIQUE constraint ensures that all values in a column (or combination of columns) are distinct.

sqlCopyEditCREATE TABLE users (
  email VARCHAR(255) UNIQUE
);

You can also create multi-column uniqueness:

sqlCopyEditCONSTRAINT unique_user_email UNIQUE (user_id, email)

Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and NULL values are usually allowed (depends on RDBMS).

---

6. CHECK Constraint

The CHECK constraint validates that data meets a specific condition.

sqlCopyEditCREATE TABLE employees (
  salary INT,
  CHECK (salary >= 0)
);

If someone tries to insert a negative salary, the operation fails.

You can also use complex conditions:

sqlCopyEditCHECK (status IN ('active', 'inactive', 'suspended'))

---

7. DEFAULT Constraint

The DEFAULT constraint assigns a default value if no value is specified during insertion.

sqlCopyEditCREATE TABLE users (
  is_active BOOLEAN DEFAULT TRUE
);

Now, if is_active is omitted, it will default to TRUE.

---

8. Combining Multiple Constraints

You can combine constraints on the same column:

sqlCopyEditCREATE TABLE accounts (
  account_id INT PRIMARY KEY,
  balance DECIMAL(10, 2) NOT NULL CHECK (balance >= 0) DEFAULT 0.00
);

This ensures no nulls, no negative balances, and initializes to zero.

---

9. Adding Constraints to Existing Tables

Use ALTER TABLE to add constraints later:

sqlCopyEditALTER TABLE users
ADD CONSTRAINT chk_age CHECK (age >= 18);

For NOT NULL:

sqlCopyEditALTER TABLE users
MODIFY age INT NOT NULL; -- MySQL

Or

sqlCopyEditALTER TABLE users
ALTER COLUMN age SET NOT NULL; -- PostgreSQL

---

10. Dropping Constraints

Constraints can be removed with ALTER TABLE:

sqlCopyEditALTER TABLE users
DROP CONSTRAINT chk_age; -- PostgreSQL, SQL Server

-- MySQL for UNIQUE
ALTER TABLE users
DROP INDEX email;

You must know the constraint name in some RDBMSs.

---

11. Naming Constraints for Clarity

Give custom names to constraints:

sqlCopyEditCONSTRAINT chk_positive_balance CHECK (balance >= 0)

Helps with debugging and managing schema migrations.

---

12. Column-Level vs Table-Level Constraints

• Column-level: Defined right after the column name
• Table-level: Defined after all columns

Example:

sqlCopyEdit-- Column-level
salary DECIMAL(10,2) CHECK (salary > 0)

-- Table-level
CHECK (salary > 0)

Use table-level for constraints involving multiple columns.

---

13. Real-World Example: Enforcing Data Validity

sqlCopyEditCREATE TABLE registrations (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INT CHECK (age >= 18),
  registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Ensures:

• Email is unique and not null
• Age is at least 18
• Default registration timestamp is set

---

14. Constraint Violation Errors

If you violate a constraint, the database throws an error like:

• ERROR: null value in column "name" violates not-null constraint
• ERROR: duplicate key value violates unique constraint
• ERROR: new row for relation violates check constraint

Always validate data before insertion or use TRY-CATCH or equivalent in application logic.

---

15. Performance Impact of Constraints

Constraints have minimal read overhead, but:

• May slow inserts/updates due to validation
• Improve query optimization (e.g., using UNIQUE for index)
• Help databases infer cardinality during planning

---

16. Difference Between UNIQUE and PRIMARY KEY

Feature	PRIMARY KEY	UNIQUE
Null allowed?	No	Yes (usually, varies)
Count per table	One	Multiple
Index behavior	Automatically indexed	Automatically indexed

---

17. Best Practices for Using Constraints

• Always use NOT NULL unless nulls are required
• Define DEFAULT values for optional fields
• Use CHECK to enforce business rules early
• Name constraints clearly for future reference
• Document constraint logic as part of schema design

---

18. Using Constraints in Insert Statements

sqlCopyEditINSERT INTO users (name) VALUES ('Alice'); -- `is_active` defaults to TRUE

Will fail if:

• name is null and NOT NULL is enforced
• A duplicate email is inserted under UNIQUE

---

19. Compatibility in Different SQL Dialects

Constraint	PostgreSQL	MySQL	SQL Server	Oracle
NOT NULL	✅	✅	✅	✅
UNIQUE	✅	✅	✅	✅
CHECK	✅	⚠️ (enforced only in InnoDB 8+)	✅	✅
DEFAULT	✅	✅	✅	✅

---

20. Summary and What’s Next

SQL constraints like NOT NULL, UNIQUE, CHECK, and DEFAULT are essential tools for enforcing data integrity at the schema level. When used correctly, they reduce bugs, improve consistency, and push validation into the database layer.