SQL DDL (Data Definition Language)
Master the commands used to define, modify, and delete the structure of your database, including tables, indexes, and constraints.
🏗️ Topic 1: What is Data Definition Language (DDL)?
**Data Definition Language (DDL)** is the language used to define and manage the **structure** (or schema) of database objects like tables, indexes, and databases.
DDL commands are permanent and **auto-commit** changes—meaning they cannot typically be undone (rolled back) once executed. They modify the *blueprints* of the database.
⚙️ Topic 2: CREATE TABLE & Constraints
The **CREATE TABLE** command is the foundational DDL operation, where you define the columns, their data types, and any associated rules (**Constraints**).
📝 Key Constraints:
- PRIMARY KEY: Uniquely identifies every row. Cannot be NULL.
- FOREIGN KEY: Links to the
PRIMARY KEYof another table, ensuring **referential integrity**. - NOT NULL: Ensures that a column must always contain a value.
- UNIQUE: Ensures all values in a column are different.
- DEFAULT: Provides a default value if none is specified.
💻 Example: Creating a Table with Constraints
CREATE TABLE Projects ( ProjectID INT PRIMARY KEY, Title VARCHAR(100) NOT NULL, Budget DECIMAL(10, 2), StartDate DATE DEFAULT CURRENT_DATE);
🛠️ Topic 3: Modifying Structure with ALTER TABLE
The **ALTER TABLE** command changes the schema of an existing table without deleting the data already inside it.
Common Operations:
- Adding Columns:
ALTER TABLE TableName ADD NewCol Type; - Dropping Columns:
ALTER TABLE TableName DROP COLUMN OldCol; - Modifying Columns: Changes the data type or constraints of a column.
💻 Example: Schema Modification
-- 1. Add a new column to track StatusALTER TABLE Projects ADD Status VARCHAR(50) DEFAULT 'Active';
-- 2. Increase the size of the Title columnALTER TABLE Projects ALTER COLUMN Title VARCHAR(255);
💣 Topic 4: Deleting Objects: DROP vs TRUNCATE
These commands are used for permanent deletion and should be handled with extreme care.
🗑️ Comparison (DDL Hierarchy):
DROP TABLE: Deletes the **entire table structure** and all data. The table ceases to exist.TRUNCATE TABLE: Deletes **all data rows** in the table, but keeps the empty structure intact. It's often faster thanDELETE.
💻 Example: Deletion Commands
-- Deletes the table permanentlyDROP TABLE Projects;
-- Empties the table, keeping the column headersTRUNCATE TABLE AuditLogs;
📚 Module Summary
- CREATE: Builds new objects (Tables, Indexes).
- ALTER: Modifies schema (Add/Drop/Change Columns).
- DROP: Deletes the object (Table structure + data).
- TRUNCATE: Deletes only the data rows (Keeps structure).
- Constraints: Rules like PRIMARY KEY and NOT NULL are defined during CREATE.
🤔 Interview Q&A
Tap on the questions below to reveal the answers.