🛠️ Mastering SQL DDL Commands: CREATE, ALTER, DROP, and More

When working with databases, the Data Definition Language (DDL) is your toolkit for creating and modifying database structures like tables, columns, and constraints. Whether you're building a database from scratch or evolving your schema, these commands are essential.

Let’s explore DDL commands with clear syntax examples and practical use cases.

---

🔧 1. CREATE – Build New Tables and Structures

🧱 Create a Basic Table

sqlCopyEditCREATE TABLE Student (
    S_ID NUMBER,
    Name CHAR(20),
    Marks NUMBER
);

🔑 Create a Table with Primary Key

sqlCopyEditCREATE TABLE Student (
    S_ID NUMBER PRIMARY KEY,
    Name CHAR(20),
    Marks NUMBER
);

🔗 Create a Table with Foreign Key

sqlCopyEditCREATE TABLE Orders (
    OrderID NUMBER PRIMARY KEY,
    StudentID NUMBER,
    FOREIGN KEY (StudentID) REFERENCES Student(S_ID)
);

📋 Create a Table from Another Table

This copies structure and data:

sqlCopyEditCREATE TABLE TopStudents AS
SELECT * FROM Student
WHERE Marks > 75;

---

🛠️ 2. ALTER – Modify Existing Tables

You can add or change columns, rename columns, or manage constraints.

➕ Add a Column

sqlCopyEditALTER TABLE Student
ADD Age NUMBER;

📝 Modify Column Data Type

sqlCopyEditALTER TABLE Student
MODIFY Name VARCHAR(30);

✏️ Rename a Column

sqlCopyEditALTER TABLE Student
RENAME COLUMN Name TO FirstName;

❌ Drop a Column

sqlCopyEditALTER TABLE Student
DROP COLUMN Age;

---

🔐 Adding Constraints AFTER Table Creation

🔑 Add Primary Key

sqlCopyEditALTER TABLE Student
ADD CONSTRAINT pk_student PRIMARY KEY (S_ID);

🔗 Add Foreign Key

sqlCopyEditALTER TABLE Orders
ADD CONSTRAINT fk_orders_student
FOREIGN KEY (StudentID) REFERENCES Student(S_ID);

📏 Add Unique Constraint

sqlCopyEditALTER TABLE Student
ADD CONSTRAINT unique_name UNIQUE (Name);

---

🔓 Dropping Constraints

sqlCopyEditALTER TABLE Student
DROP CONSTRAINT pk_student;

---

🧹 3. DROP – Permanently Delete Tables or Views

Deletes the table and all its data and structure.

sqlCopyEditDROP TABLE Student;

If the table has dependent constraints:

sqlCopyEditDROP TABLE Student CASCADE CONSTRAINTS;

---

🧽 4. TRUNCATE – Wipe All Rows (Keep Structure)

sqlCopyEditTRUNCATE TABLE Student;

⚠️ This is faster than DELETE but cannot be rolled back.

---

🔄 5. RENAME – Rename Tables

sqlCopyEditRENAME Student TO StudentRecords;

---

📊 Quick Reference Summary

---

🎓 Final Tips

• Always name your constraints for easier management.

• Use ALTER to evolve your schema instead of dropping and recreating tables.

• For development, test your DDL changes in a safe environment before applying them to production.