Session 5.2 – DDL Commands: CREATE, ALTER, DROP

Module 5: PHP Database Connectivity | Duration: 1 hr

Learning Objectives

By the end of this session, students will be able to:

Understand Data Definition Language (DDL) and its purpose
Create databases and tables using CREATE statements
Define and implement various constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, etc.)
Modify table structures using ALTER TABLE commands
Remove databases and tables using DROP statements
Create and manage indexes for performance optimization

Introduction to DDL

Data Definition Language (DDL) is a subset of SQL used to define and manage database structures. DDL commands allow you to create, modify, and delete database objects such as databases, tables, indexes, and views.

Main DDL Commands

CREATE: Creates new database objects (databases, tables, indexes)
ALTER: Modifies existing database object structures
DROP: Deletes database objects permanently
TRUNCATE: Removes all records from a table but keeps the structure
RENAME: Renames database objects

Note: DDL commands are auto-committed, meaning changes are permanent and cannot be rolled back.

CREATE DATABASE

The CREATE DATABASE statement is used to create a new database in MySQL.

Syntax

   
CREATE DATABASE [IF NOT EXISTS] database_name
[CHARACTER SET charset_name]
[COLLATE collation_name];

Examples


-- Create a simple database
CREATE DATABASE school;

-- Create database with character set
CREATE DATABASE ecommerce
CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;

-- Create database only if it doesn't exist
CREATE DATABASE IF NOT EXISTS blog;

-- Show all databases
SHOW DATABASES;

-- Select database to use
USE school;

CREATE TABLE

The CREATE TABLE statement is used to create a new table in the database.

Basic Syntax

   
CREATE TABLE [IF NOT EXISTS] table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
    [table_constraints]
);

Simple Table Example

   
-- Create a users table
CREATE TABLE users (
    user_id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Complex Table Example

   
-- Create a products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    product_name VARCHAR(100) NOT NULL,
    description TEXT,
    price DECIMAL(10, 2) NOT NULL,
    stock_quantity INT DEFAULT 0,
    category_id INT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (product_id),
    INDEX idx_category (category_id)
);

Table with Foreign Key

   
-- Create orders table with foreign key
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
    total_amount DECIMAL(10, 2) NOT NULL,
    status ENUM('pending', 'processing', 'completed', 'cancelled') DEFAULT 'pending',
    FOREIGN KEY (user_id) REFERENCES users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

Constraints

Constraints are rules applied to columns to enforce data integrity and maintain database consistency.

PRIMARY KEY

Uniquely identifies each record in a table. A table can have only one primary key.

   
-- Single column
id INT PRIMARY KEY

-- Auto-increment
id INT AUTO_INCREMENT PRIMARY KEY

-- Composite primary key
PRIMARY KEY (student_id, course_id)

FOREIGN KEY

Creates a relationship between tables. Ensures referential integrity.

   
-- Foreign key constraint
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE

UNIQUE

Ensures all values in a column are different.

   
-- Single column unique
email VARCHAR(100) UNIQUE

-- Composite unique
UNIQUE (email, username)

NOT NULL

Ensures a column cannot have NULL values.

   
-- Not null constraint
username VARCHAR(50) NOT NULL
password VARCHAR(255) NOT NULL

DEFAULT

Sets a default value for a column when no value is specified.

   
-- Default value
status VARCHAR(20) DEFAULT 'active'
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

CHECK

Ensures values in a column satisfy a specific condition.

   
-- Check constraint
age INT CHECK (age >= 18)
price DECIMAL(10,2) CHECK (price > 0)

ALTER TABLE

The ALTER TABLE statement is used to modify the structure of an existing table.

Add Column

   
-- Add a single column
ALTER TABLE users
ADD phone VARCHAR(20);

-- Add column with constraints
ALTER TABLE users
ADD date_of_birth DATE,
ADD address VARCHAR(255);

-- Add column at specific position
ALTER TABLE users
ADD middle_name VARCHAR(50) AFTER first_name;

-- Add column as first
ALTER TABLE users
ADD id INT AUTO_INCREMENT PRIMARY KEY FIRST;

Modify Column

   
-- Change column data type
ALTER TABLE users
MODIFY username VARCHAR(100);

-- Change column with constraints
ALTER TABLE users
MODIFY email VARCHAR(150) NOT NULL UNIQUE;

-- Rename and change column (CHANGE)
ALTER TABLE users
CHANGE username user_name VARCHAR(100);

Drop Column

   
-- Remove a column
ALTER TABLE users
DROP COLUMN middle_name;

-- Drop multiple columns
ALTER TABLE users
DROP COLUMN address,
DROP COLUMN phone;

Add/Drop Constraints

   
-- Add primary key
ALTER TABLE users
ADD PRIMARY KEY (user_id);

-- Add foreign key
ALTER TABLE orders
ADD CONSTRAINT fk_user
FOREIGN KEY (user_id) REFERENCES users(user_id);

-- Add unique constraint
ALTER TABLE users
ADD UNIQUE (email);

-- Drop foreign key
ALTER TABLE orders
DROP FOREIGN KEY fk_user;

-- Drop primary key
ALTER TABLE users
DROP PRIMARY KEY;

Rename Table

   
-- Rename table using ALTER
ALTER TABLE users RENAME TO customers;

-- Rename using RENAME statement
RENAME TABLE customers TO users;

DROP Statements

DROP statements are used to delete database objects permanently.

Warning: DROP operations are irreversible. Always backup data before dropping objects.

DROP DATABASE

   
-- Drop a database
DROP DATABASE school;

-- Drop if exists (prevents error)
DROP DATABASE IF EXISTS school;

DROP TABLE

   
-- Drop a table
DROP TABLE users;

-- Drop if exists
DROP TABLE IF EXISTS users;

-- Drop multiple tables
DROP TABLE IF EXISTS users, orders, products;

TRUNCATE TABLE

   
-- Remove all records but keep structure
TRUNCATE TABLE users;

-- Faster than DELETE, resets AUTO_INCREMENT
TRUNCATE TABLE logs;

Note: TRUNCATE is faster than DELETE as it doesn't log individual row deletions and cannot be rolled back.

Indexes

Indexes improve the speed of data retrieval operations on database tables. They work like a book's index, allowing the database to find data without scanning every row.

Creating Indexes

   
-- Create simple index
CREATE INDEX idx_username ON users(username);

-- Create unique index
CREATE UNIQUE INDEX idx_email ON users(email);

-- Create composite index
CREATE INDEX idx_name ON users(first_name, last_name);

-- Create index using ALTER TABLE
ALTER TABLE products
ADD INDEX idx_category (category_id);

-- Create full-text index
CREATE FULLTEXT INDEX idx_description ON products(description);

Dropping Indexes

   
-- Drop index
DROP INDEX idx_username ON users;

-- Drop index using ALTER TABLE
ALTER TABLE users
DROP INDEX idx_username;

Viewing Indexes

   
-- Show all indexes on a table
SHOW INDEXES FROM users;

-- Show indexes from specific database
SHOW INDEXES FROM users IN mydb;

Index Best Practices

Index columns used frequently in WHERE clauses
Index columns used in JOIN conditions
Don't over-index - indexes slow down INSERT/UPDATE operations
Use composite indexes for queries with multiple conditions
Primary keys are automatically indexed

Session Summary

Key Points

DDL commands define and manage database structures (CREATE, ALTER, DROP)
CREATE DATABASE and CREATE TABLE establish new database objects
Constraints enforce data integrity (PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK)
ALTER TABLE modifies existing table structures (add, modify, drop columns)
DROP statements permanently delete database objects
TRUNCATE removes all data but preserves table structure
Indexes improve query performance but should be used judiciously
Foreign keys create relationships between tables with referential integrity

Next Session Preview

In the next session, we will explore DML (Data Manipulation Language) commands including INSERT, UPDATE, DELETE, and SELECT statements for working with data.

Previous Session Course Home Next Session