Skip to content

03_Constraints_NOT_NULL_UNIQUE.md

Latest commit

 

History

History
135 lines (84 loc) · 6.28 KB

03_Constraints_NOT_NULL_UNIQUE.md

File metadata and controls

135 lines (84 loc) · 6.28 KB

🚧 Topic 4.3: SQL Constraints Deep Dive - NOT NULL & UNIQUE

You are absolutely right. A professional database isn't just a container holding data—it is a mathematically armed fortress designed to physically reject bad, buggy, or malicious data.

To arm our fortress, we must bolt Constraints directly onto our columns during CREATE TABLE. In this topic, we will Master the two foundational pillars of data hygiene: NOT NULL and UNIQUE.

1. Definition

  1. NOT NULL Constraint: A strict mathematical rule that physically forces a column to explicitly reject empty data. Every single row inserted must provide a value for this column.
  2. UNIQUE Constraint: A strict mathematical rule that physically forces a column to reject duplicate data. No two rows can ever share the exact same text or number in this column.

2. Why This Concept Exists

When an application scales to 1,000,000 users, frontend validation code (like JavaScript checking if a password box is empty) will eventually fail or get hacked.

If your backend code accidentally sends a user without a password into the database, Database Constraints exist perfectly at the very bottom of the technology stack to catch the error and instantly mathematically abort the insertion.

3. Why We Use It (The ACID Guard)

If you insert a row, and a single Constraint mathematically fails, the entire transaction ROLLBACKS instantly without saving a single byte to the hard drive.

  • We use NOT NULL so we never forget a user's critical data (e.g., Username, Password_Hash).
  • We use UNIQUE so we can mathematically guarantee our Alternate Keys (e.g., Email, SSN, Phone_Number).

4. When to Use Which?

  • Use NOT NULL on columns that are legally required for your business logic to function. (A user cannot exist without a login email).
  • Use UNIQUE on columns where a collision would cause a legal or security nightmare (Two players owning the same Gift_Card_Code).

5. How It Works (The UNIQUE B-Tree Engine - PRO LEVEL)

This is a senior-level architectural secret:

  1. NOT NULL is just a simple algebra check. It costs the database engine essentially zero milliseconds to process.
  2. UNIQUE, however, is radically heavy. When you tell MySQL a column is UNIQUE, the Database Engine secretly generates an entirely new Secondary B-Tree Index on the physical hard drive. When you insert a new row, MySQL must pause the insertion, jump to the hidden B-Tree, and scan the entire tree to mathematically prove your exact string doesn't already exist. While UNIQUE is required for data integrity, declaring too many columns as UNIQUE will massively bloat your hard drive and slow down your application's write-speed!

6. Syntax / Implementation (Cheat Sheet)

You declare these constraints exactly on the same line as the Column Definition.

CREATE TABLE table_name (
    column_name DATA_TYPE CONSTRAINT_1 CONSTRAINT_2
);

7. Real-Life Examples

The Digital DMV Application:

  • You are building a table Drivers.
  • Name VARCHAR(100) NOT NULL: Everyone must have a name. (But two people can legally be named "John").
  • State_ID VARCHAR(20) UNIQUE NOT NULL: The Government-issued ID. Everyone absolutely must have one (NOT NULL), and no two people can possibly have the same ID (UNIQUE).

8. SQL Examples (MySQL Execution)

Let's formally define the constraints stacking them seamlessly together:

CREATE TABLE Users (
    -- Primary Keys are basically both of these commands secretly combined!
    User_ID INT PRIMARY KEY AUTO_INCREMENT,
    
    -- STACKING: This column must be filled, AND must be 100% unique globally
    Username VARCHAR(50) NOT NULL UNIQUE,
    
    -- This column MUST be filled, but duplicates are totally fine (Two users can have the same password)
    Password_Hash CHAR(60) NOT NULL,
    
    -- This column CAN be left entirely blank! But if filled, it must be unique.
    Phone_Number VARCHAR(20) UNIQUE,
    
    -- This column can be left blank, and duplicates are fine. (The default fallback).
    Biography TEXT
);

-- ❌ FATAL ERROR: Trying to insert NULL into a NOT NULL column
INSERT INTO Users (Username, Password_Hash) VALUES (NULL, 'hashxyz');

-- ❌ FATAL ERROR: Trying to insert a duplicate into a UNIQUE column
INSERT INTO Users (Username, Password_Hash) VALUES ('John', 'hash123');
INSERT INTO Users (Username, Password_Hash) VALUES ('John', 'hash456');

9. Common Mistakes

  • Assuming UNIQUE prevents NULLs (The NULL Paradox): This is the deadliest junior developer logic trap in all of SQL. If you declare a column as just UNIQUE (like the Phone Number in the example above), a user can absolutely insert NULL into that database 50,000 times natively without throwing any errors! Why? Because in pure Relational Algebra, NULL physically means "Unknown". And algebraically, Unknown != Unknown. Therefore, NULL is mathematically never considered a duplicate of another NULL!
  • Conclusion: If you want a column to be perfectly unique and completely filled, you absolutely must explicitly stack them: UNIQUE NOT NULL.

10. Tips & Best Practices (Pro-Level)

Constraint Naming Strategy: If you strictly use UNIQUE as shown above, MySQL generates a terrible, random alphanumeric name for the constraint within its internal memory (e.g., UQ_1x9z2).

If your backend server API crashes on a Friday night, the error log says: "SQL Error: Constraint UQ_1x9z2 violated_!" You have absolutely no idea what column threw the bug.

The Pro Fix: Always explicitly name your advanced UNIQUE rules using the CONSTRAINT keyword so your debugging logs are instantly readable!

CREATE TABLE Products (
    Product_Code VARCHAR(50),
    
    -- We explicitly give this rule a human-readable title!
    CONSTRAINT unique_barcode UNIQUE (Product_Code)
);

11. Mini Practice Tasks

  • Task 1: Write the exact DDL line to create an Email column (capable of holding 100 characters). It must never physically be empty, and it must mathematically never allow duplicates.
  • Task 2: Why does writing a UNIQUE constraint natively slow down your database INSERT commands, whereas NOT NULL does not? Provide the physical hard-drive B-Tree explanation from Section 5.