← Back to Chapters

MySQL CREATE and DROP TABLE

?️ CREATE & ?️ DROP TABLE

MySQL • DDL • Structure Management

? Quick Overview

Database tables have a lifecycle: they are defined, used to store data, and eventually removed.

  • CREATE TABLE: Defines the structure (columns, data types, constraints) for storing data.
  • DROP TABLE: Permanently removes the structure and all data contained within it.

? Key Concepts

  • DDL (Data Definition Language): Both commands modify the database structure, not just the rows inside.
  • Constraints: Rules defined during creation (like PRIMARY KEY, UNIQUE) that ensure data integrity.
  • Destructive Operation: Dropping a table is immediate and irreversible without a backup.
  • Dependencies: You cannot drop a table if another table refers to it (via Foreign Keys) unless you handle the relationship first.

1️⃣ ?️ CREATE TABLE

Use this statement to define a new container for your data.

? View Code Example 
-- Syntax Template
CREATE TABLE table_name (
column1 datatype constraints,
column2 datatype constraints,
...
);

-- Real Example: Creating a Users table
CREATE TABLE Users (
id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

Common Data Types

  • INT / BIGINT: Whole numbers (IDs, counts).
  • VARCHAR(n): Variable text up to n characters (names, emails).
  • DATE / DATETIME: Time-based data.
  • DECIMAL(10,2): Exact money values.
? View Code Example 
-- Parent Table
CREATE TABLE Departments (
dept_id INT PRIMARY KEY,
dept_name VARCHAR(50)
);

-- Child Table with Foreign Key
CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
);

2️⃣ ?️ DROP TABLE

Use this statement to permanently delete a table. This is often done during cleanup or restructuring.

⚠️ Warning: DROP TABLE cannot be undone. All rows, indexes, and structure are lost immediately.
? View Code Example 
-- Basic Drop (Will error if table doesn't exist)
DROP TABLE Users;

-- Safe Drop (Prevents error if table is missing)
DROP TABLE IF EXISTS Users;

-- Drop Multiple Tables at once
DROP TABLE Employees, Departments;

? The Full Lifecycle

This flow demonstrates creating a table, using it, and then cleaning it up.

? View Code Example 
-- 1. DEFINE: Create a temporary log table
CREATE TABLE AppLogs (
log_id INT AUTO_INCREMENT PRIMARY KEY,
message TEXT,
level VARCHAR(10) DEFAULT 'INFO'
);

-- 2. USE: Insert data into the structure
INSERT INTO AppLogs (message, level) 
VALUES ('System started', 'INFO'), ('Connection failed', 'ERROR');

-- 3. VERIFY: Check the data
SELECT * FROM AppLogs;

-- 4. CLEANUP: Remove the table completely
DROP TABLE AppLogs;

✅ Best Practices

  • Naming: Use clear, plural names for tables (e.g., Customers, not Cust).
  • Primary Keys: Always define a PRIMARY KEY for every table to uniquely identify rows.
  • Backups: Always backup your database (mysqldump) before running DROP commands in production.
  • Scripts: Use DROP TABLE IF EXISTS at the start of setup scripts to ensure a clean slate before creating tables.

? Try It Yourself

  • Task 1: Create a table named Products with columns for id, name, and price.
  • Task 2: Insert 3 products into the table.
  • Task 3: Try to create the same table again without dropping it first. (Observe the "Table already exists" error).
  • Task 4: Drop the table using DROP TABLE Products; and verify it is gone using SHOW TABLES;.