SQL Fundamentals in SQL

The Relational Database Blueprint In production software architecture, application code needs a permanent, structured layer to store and manage its data safely. While system memory (RAM) is fast, it is completely volatile—all data vanishes the moment a server restarts or a process crashes. A Database ensures that user sessions, financial records, and operational logs remain completely safe and persistent across system recycles. 1. Foundations: SQL and Database Topologies SQL (Structured Query Language): The universal domain-specific programming language used to communicate with, manage, and query relational database management systems. Database Engine (DB): The underlying software service responsible for organizing, storing, indexing, and maintaining data assets safely on a storage drive. Core Database Classifications Databases are broadly divided into two primary structural categories based on how they organize and represent data: DATABASE ARCHITECTURES │ ┌──────────────────────────┴──────────────────────────┐ ▼ ▼ Relational (RDBMS) Non-Relational (NoSQL) • Strict tabular layout (Tables/Rows/Columns) • Flexible structures (JSON Documents, Key-Value) • Strict structural schemas • Schema-less, dynamic scaling • Strong ACID compliance • Optimized for rapid scaling & high throughput • Examples: PostgreSQL, MySQL, SQLite • Examples: MongoDB, Redis 2. Relational Database Management System (RDBMS) Architecture An RDBMS is a specific type of database engine that organizes data into a structured grid of interconnected tables. This structural paradigm models data by mapping physical entities and business logic into an organized matrix: Table (Relation): A structured, two-dimensional grid container that represents a specific business entity category (e.g., a Users table or a PurchaseOrders table). Row (Tuple / Record): A single horizontal entry inside a table representing one individual instance of that entity (e.g., one specific user profile). Column (Attribute / Field): A vertical structural field that defines a specific property type belonging to the entity schema layout (e.g., email_address , created_at ). The Structural Schema & ACID Guardrails Relational engines enforce a rigid, pre-defined Schema —meaning every single row written to a table must strictly match that table's structural column definition rules. This structure allows RDBMS engines to guarantee ACID compliance , which ensures your database operations remain completely safe: A - Atomicity: Guarantees that a multi-step database transaction executes completely as a single unit or fails entirely—there are no partial writes. If a script updates a user balance but crashes before saving the matching transaction log, the entire operation is rolled back completely. C - Consistency: Enforces that all data written to a table must pass your structural database validation rules and constraints perfectly before it is saved to disk. I - Isolation: Ensures that multiple transactions executing concurrently cannot interfere with or corrupt each other's data states. D - Durability: Guarantees that once a transaction is successfully committed, its data is safely written to permanent storage and will not be lost, even if a total system power failure occurs immediately after. 3. The Relational Key Hierarchy To keep data structured and easily searchable, an RDBMS relies on a strict hierarchy of structural keys. These keys identify rows uniquely and link different tables together cleanly. THE DATABASE KEY FAMILY │ ┌──────────────────┴──────────────────┐ ▼ ▼ Super Key Foreign Key (Uniquely identifies rows) (Links child table │ to parent table) ▼ Candidate Key (Minimal Super Key) │ ┌──────────┴──────────┐ ▼ ▼ Primary Key Unique Key (Chosen Identifier) (Prevents duplicate values; allows NULL) A. Super Key Any single column or combination of multiple columns that can uniquely identify an individual row inside a table. A table can have dozens of different Super Key combinations. B. Candidate Key A minimal Super Key with zero redundant columns. It contains the absolute smallest number of attributes required to uniquely identify a single record. Zero redundant columns means every column in the key is necessary . If you can remove even one column and the remaining columns still uniquely identify each row, then that removed column was redundant (unnecessary) . C. Primary Key (PK) The single Candidate Key chosen by the software architect to act as the official, unique identifier for every row in a table. The Rule: A table can have exactly one Primary Key. It can never contain duplicate values, and it cannot contain NULL values . D. Composite Key A Primary Key that is built by combining two or more columns together to form a unique identifier, used when a single column isn't enough to guarantee uniqueness on its own. SQL -- Example: Mapping items to an order. Neither order_id nor product_id is unique on its own, -- but combining them creates a unique composite identifier for that specific row item. PRIMARY KEY (order_id, product_id) E. Unique Key Enforces that all values inside a target column must be completely unique across the entire table (e.g., ensuring two users cannot register with the exact same email_address ). Primary Key vs. Unique Key: Unlike a Primary Key, a table can contain multiple separate Unique Key columns, and Unique Key columns are allowed to accept NULL values (representing an unassigned or missing state). F. Foreign Key (FK) A column (or group of columns) in a child table that points directly to a Primary Key inside a parent table. This constraint forms the structural link that binds different tables together, enforcing Referential Integrity . Referential Integrity Guardrail: The database engine will automatically block you from inserting a row into a child table if its Foreign Key pointer doesn't exist in the parent table. It will also block you from deleting a parent row if child records are still actively pointing to it. 4. Structural Database Constraints Constraints are validation rules applied directly to a column's schema definition. The database engine enforces these rules at the hardware layer, blocking any bad or malformed data writes before they touch your storage drive. NOT NULL : Forces the column to always receive a valid data value. The engine will throw a database error if you attempt to save a row with this field left empty or unassigned. UNIQUE : Guarantees that no two rows can share the exact same value inside this column. DEFAULT : Automatically assigns a fallback value to the column if an insert command doesn't specify one. CHECK : Evaluates a custom boolean validation rule before allowing a write operation to succeed. AUTO_INCREMENT (MySQL) / SERIAL (PostgreSQL): Instructs the database engine to automatically generate a sequential integer counter (1, 2, 3...) for this field whenever a new row is inserted, commonly used to manage Primary Keys cleanly. Understanding NULL Values In SQL, NULL does not mean a value is equal to zero, an empty string "" , or a blank space. NULL represents a completely unknown, unassigned, or missing data state . Because NULL signifies an unknown state, you can never check for it using standard mathematical equality operators like = NULL or != NULL . Instead, you must use the specialized SQL operators IS NULL and IS NOT NULL . SQL -- ANTI-PATTERN: SELECT * FROM Users WHERE profile_bio = NULL; -- Returns 0 rows safely -- PRODUCTION STANDARD: SELECT * FROM Users WHERE profile_bio IS NULL; 5. SQL Data Types Quick-Reference When building tables, choosing the correct data type for each column keeps your database fast and prevents wasted disk storage space. Category SQL Data Type Operational Storage Behavior Critical Production Guardrail Fixed Text CHAR(X) Allocates a fixed number of bytes every time. If you declare CHAR(10) but write a 3-character string, the engine pads the rest with blank spaces. Ideal for fixed-length codes like currency tags ( INR , USD ) or system hashes. Variable Text VARCHAR(X) Allocates bytes dynamically based on the actual text string length, up to a maximum limit of X . Use this for general text fields like names or email addresses to save disk storage space. Integers INT / BIGINT Stores standard whole numbers. BIGINT uses 8 bytes of storage to hold massively large number boundaries. Always use BIGINT for auto-incrementing Primary Keys in high-traffic tables to prevent running out of ID numbers. Exact Decimals NUMERIC(P, S) / DECIMAL Stores exact numbers with a fixed number of decimal places. P represents total digits, S represents digits after the decimal point. Critical Guardrail: Never use floating-point types like FLOAT or REAL to store financial balances. Floats suffer from precision rounding errors. Always use NUMERIC or DECIMAL for currency tracking. Temporal Dates TIMESTAMP / TIMESTAMPTZ Stores combined date and clock time values. Always prefer TIMESTAMPTZ (Timestamp with Time Zone) in production to ensure times stay accurate regardless of server location migrations. AUTO_INCREMENT is a MySQL-specific feature used to generate sequential numeric values automatically, whereas GENERATED ALWAYS AS IDENTITY is a SQL-standard feature mainly used in PostgreSQL and other modern databases for automatic identity generation with stricter control.AUTO_INCREMENT allows inserting id as well but  GENERATED ALWAYS AS IDENTITY does not  allow this . Data Definition Language (DDL) Implementation Blueprint Even when UNIQUE is defined directly at column level, the database internally creates a constraint that can be dropped later. Using CONSTRAINT explicitly is mainly preferred for giving readable custom names and better maintainability. SQL -- Designing an interconnected, production-ready schema block CREATE TABLE companies ( company_id INT GENERATED ALWAYS AS IDENTITY, -- Auto-incrementing identifier company_name VARCHAR(150) NOT NULL, tax_identifier CHAR(10) NOT NULL, is_active BOOLEAN DEFAULT TRUE, PRIMARY KEY (company_id), CONSTRAINT unique_tax_id UNIQUE (tax_identifier) ); CREATE TABLE corporate_nodes ( node_id INT GENERATED ALWAYS AS IDENTITY, parent_company_id INT NOT NULL, -- Will act as our foreign key link assigned_ip_address VARCHAR(45) NOT NULL, traffic_weight INT DEFAULT 100, PRIMARY KEY (node_id), -- Link child company ID pointer directly back to parent primary company table FOREIGN KEY (parent_company_id) REFERENCES companies(company_id) ON DELETE CASCADE, -- Custom validation rule to protect structural calculations CONSTRAINT check_positive_weight CHECK (traffic_weight > 0) );