DDL

What is Data Definition Language (DDL)?

Data Definition Language (DDL) is a subset of SQL (Structured Query Language) used to define and manage the structure of database objects. DDL commands enable database administrators and developers to create, modify, and remove database structures such as tables, indexes, views, and constraints.

Unlike DML (Data Manipulation Language) which deals with data operations, DDL focuses on the database schema and structure itself.

Purpose of DDL

DDL commands serve several critical purposes in database management:

1. Database Structure Definition: Define the logical structure of the database
2. Schema Management: Create and modify database schemas
3. Object Creation: Establish tables, views, and other database objects
4. Constraint Definition: Implement rules to maintain data integrity
5. Access Control: Set up security mechanisms
6. Storage Management: Configure physical storage parameters

Key DDL Commands

1. CREATE

The CREATE command is used to create new database objects.

CREATE DATABASE

Creates a new database.

CREATE DATABASE university;

CREATE TABLE

Creates a new table with specified columns and constraints.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    email VARCHAR(100) UNIQUE,
    major VARCHAR(50),
    enrollment_date DATE DEFAULT CURRENT_DATE
);

CREATE INDEX

Creates an index on one or more columns to improve query performance.

-- Simple index
CREATE INDEX idx_student_last_name ON students(last_name);

-- Composite index
CREATE INDEX idx_student_name ON students(last_name, first_name);

-- Unique index
CREATE UNIQUE INDEX idx_student_email ON students(email);

CREATE VIEW

Creates a virtual table based on the result set of a SQL statement.

CREATE VIEW student_info AS
SELECT student_id, first_name, last_name, major
FROM students
WHERE enrollment_date > '2022-01-01';

CREATE SCHEMA

Creates a new schema (namespace) to organize database objects.

CREATE SCHEMA academic;

CREATE SEQUENCE

Creates a sequence generator (auto-increment).

CREATE SEQUENCE student_id_seq
START WITH 1000
INCREMENT BY 1
NO MAXVALUE
CACHE 20;

CREATE TRIGGER

Creates a trigger (automated action) that executes when specified events occur.

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON students
FOR EACH ROW
SET NEW.last_updated = CURRENT_TIMESTAMP;

CREATE PROCEDURE/FUNCTION

Creates a stored procedure or function.

CREATE PROCEDURE enroll_student(
    IN p_student_id INT,
    IN p_course_id INT
)
BEGIN
    INSERT INTO enrollments (student_id, course_id, enrollment_date)
    VALUES (p_student_id, p_course_id, CURRENT_DATE);
END;

2. ALTER

The ALTER command modifies existing database objects.

ALTER TABLE

Modifies an existing table structure.

-- Add a new column
ALTER TABLE students ADD COLUMN graduation_year INT;

-- Modify a column
ALTER TABLE students MODIFY COLUMN email VARCHAR(150);

-- Drop a column
ALTER TABLE students DROP COLUMN graduation_year;

-- Rename a column
ALTER TABLE students RENAME COLUMN date_of_birth TO birth_date;

-- Add a constraint
ALTER TABLE students ADD CONSTRAINT chk_valid_email 
CHECK (email LIKE '%@%.%');

-- Drop a constraint
ALTER TABLE students DROP CONSTRAINT chk_valid_email;

-- Rename a table
ALTER TABLE students RENAME TO university_students;

ALTER DATABASE

Modifies database settings.

ALTER DATABASE university SET default_tablespace = university_space;

ALTER INDEX

Modifies an existing index.

ALTER INDEX idx_student_name RENAME TO idx_student_full_name;

ALTER SEQUENCE

Modifies sequence properties.

ALTER SEQUENCE student_id_seq RESTART WITH 2000;

ALTER VIEW

Changes the definition of a view.

ALTER VIEW student_info AS
SELECT student_id, first_name, last_name, major, enrollment_date
FROM students
WHERE enrollment_date > '2023-01-01';

3. DROP

The DROP command removes database objects.

DROP TABLE

Removes a table and all its data.

DROP TABLE students;

-- With IF EXISTS to avoid errors if table doesn't exist
DROP TABLE IF EXISTS students;

-- Specify cascade to automatically drop dependent objects
DROP TABLE students CASCADE;

DROP DATABASE

Removes an entire database.

DROP DATABASE university;

DROP INDEX

Removes an index.

DROP INDEX idx_student_last_name;

DROP VIEW

Removes a view.

DROP VIEW student_info;

DROP SCHEMA

Removes a schema.

DROP SCHEMA academic CASCADE;

DROP SEQUENCE

Removes a sequence.

DROP SEQUENCE student_id_seq;

DROP TRIGGER

Removes a trigger.

DROP TRIGGER update_timestamp;

DROP PROCEDURE/FUNCTION

Removes a stored procedure or function.

DROP PROCEDURE enroll_student;

4. TRUNCATE

The TRUNCATE command quickly removes all rows from a table without logging individual row deletions.

TRUNCATE TABLE enrollment_history;

-- Multiple tables
TRUNCATE TABLE enrollment_history, grade_history;

-- With restart identity (reset sequences)
TRUNCATE TABLE students RESTART IDENTITY;

5. COMMENT

The COMMENT command adds explanatory notes to database objects.

COMMENT ON TABLE students IS 'Contains all student records';
COMMENT ON COLUMN students.student_id IS 'Unique identifier for each student';

6. RENAME

The RENAME command changes the name of a database object.

-- Some databases use this syntax for renaming
RENAME TABLE students TO university_students;

Constraints in DDL

Constraints are rules enforced on data columns in tables to ensure data integrity. They are typically defined using DDL statements.

Primary Key Constraint

Uniquely identifies each record in a table.

-- Inline syntax
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

-- Table constraint syntax
CREATE TABLE students (
    student_id INT,
    first_name VARCHAR(50),
    PRIMARY KEY (student_id)
);

-- Adding later with ALTER TABLE
ALTER TABLE students ADD PRIMARY KEY (student_id);

Foreign Key Constraint

Ensures referential integrity between two tables.

-- Inline syntax
CREATE TABLE enrollments (
    enrollment_id INT PRIMARY KEY,
    student_id INT REFERENCES students(student_id),
    course_id INT
);

-- Table constraint syntax
CREATE TABLE enrollments (
    enrollment_id INT,
    student_id INT,
    course_id INT,
    PRIMARY KEY (enrollment_id),
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

-- Adding later with ALTER TABLE
ALTER TABLE enrollments 
ADD CONSTRAINT fk_student 
FOREIGN KEY (student_id) REFERENCES students(student_id);

Unique Constraint

Ensures all values in a column or combination of columns are distinct.

-- Inline syntax
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    email VARCHAR(100) UNIQUE
);

-- Table constraint syntax
CREATE TABLE students (
    student_id INT,
    email VARCHAR(100),
    PRIMARY KEY (student_id),
    UNIQUE (email)
);

-- Adding later with ALTER TABLE
ALTER TABLE students ADD CONSTRAINT uq_email UNIQUE (email);

Check Constraint

Ensures all values in a column satisfy certain conditions.

-- Inline syntax
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    age INT CHECK (age >= 17)
);

-- Table constraint syntax
CREATE TABLE students (
    student_id INT,
    age INT,
    PRIMARY KEY (student_id),
    CONSTRAINT chk_valid_age CHECK (age >= 17)
);

-- Adding later with ALTER TABLE
ALTER TABLE students 
ADD CONSTRAINT chk_valid_age CHECK (age >= 17);

Not Null Constraint

Ensures a column cannot contain NULL values.

-- Inline syntax
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL
);

-- Modifying with ALTER TABLE
ALTER TABLE students MODIFY first_name VARCHAR(50) NOT NULL;

Default Constraint

Provides a default value when none is specified.

-- Inline syntax
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    enrollment_date DATE DEFAULT CURRENT_DATE
);

-- Adding later with ALTER TABLE
ALTER TABLE students 
ALTER COLUMN enrollment_date SET DEFAULT CURRENT_DATE;

Schema Management with DDL

Creating Schemas

Schemas provide a way to organize database objects and control access.

-- Create a new schema
CREATE SCHEMA academic;

-- Create a schema for a specific user
CREATE SCHEMA AUTHORIZATION professor_smith;

-- Create a table in a specific schema
CREATE TABLE academic.courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(100)
);

Managing Object Namespaces

-- Set the search path (PostgreSQL)
SET search_path TO academic, public;

-- Qualified object names
SELECT * FROM academic.courses;

Transaction Control with DDL

Some databases treat DDL statements differently regarding transactions:

Oracle

In Oracle, DDL statements implicitly commit any pending transactions.

BEGIN
    INSERT INTO students (student_id, first_name) VALUES (1, 'John');
    -- The following CREATE TABLE will automatically commit the INSERT
    CREATE TABLE new_table (id INT);
END;

PostgreSQL and SQL Server

PostgreSQL and SQL Server allow DDL statements to be part of transactions.

BEGIN TRANSACTION;
    INSERT INTO students (student_id, first_name) VALUES (1, 'John');
    CREATE TABLE new_table (id INT);
    -- Both operations will be committed or rolled back together
COMMIT;

Temporary Tables

DDL allows creating temporary tables that exist only for the duration of a session or transaction.

-- Session-level temporary table
CREATE TEMPORARY TABLE temp_students (
    student_id INT,
    name VARCHAR(100)
);

-- Transaction-level temporary table (SQL Server)
CREATE TABLE #temp_students (
    student_id INT,
    name VARCHAR(100)
);

Partitioning with DDL

Table partitioning divides large tables into smaller, more manageable pieces.

-- Range partitioning example (PostgreSQL)
CREATE TABLE sales (
    sale_date DATE,
    amount DECIMAL(10,2)
) PARTITION BY RANGE (sale_date);

CREATE TABLE sales_2022 PARTITION OF sales
    FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');

CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

Best Practices for Using DDL

1. Script and Version DDL Changes

   • Keep DDL statements in version-controlled scripts
   • Document changes with comments
   • Follow a consistent naming convention

2. Use IF EXISTS / IF NOT EXISTS Clauses

   • Prevent errors when objects do or don’t exist
   • Make scripts more robust and rerunnable

   DROP TABLE IF EXISTS students;
   CREATE TABLE IF NOT EXISTS students (student_id INT PRIMARY KEY);

3. Plan for Data Migration

   • Consider existing data when altering tables
   • Use temporary tables or staging areas for complex changes

4. Include Constraints

   • Define appropriate constraints to maintain data integrity
   • Name constraints explicitly for easier management

5. Consider Performance Implications

   • Understand how DDL operations affect database performance
   • Schedule major DDL changes during low-traffic periods

6. Test DDL Changes

   • Test DDL scripts in development environments first
   • Verify that applications work correctly with the new structure

7. Use Transactions Where Appropriate

   • Wrap related DDL statements in transactions when supported
   • Be aware of database-specific transaction behavior

8. Manage Dependencies

   • Consider object dependencies when dropping or altering objects
   • Use CASCADE options carefully

9. Document Database Schema

   • Use COMMENT statements to document objects
   • Consider generating schema documentation

10. Regular Maintenance

    • Rebuild indexes periodically
    • Update statistics after significant data changes
    • Monitor and manage storage usage

DDL Examples for Common Database Tasks

Creating a Complete Database Structure

-- Create database
CREATE DATABASE school;

-- Create schemas
CREATE SCHEMA academic;
CREATE SCHEMA administration;

-- Create tables with constraints
CREATE TABLE academic.departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL,
    building VARCHAR(50),
    budget DECIMAL(12,2) CHECK (budget >= 0)
);

CREATE TABLE academic.professors (
    professor_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    hire_date DATE DEFAULT CURRENT_DATE,
    salary DECIMAL(10,2) CHECK (salary > 0),
    FOREIGN KEY (department_id) REFERENCES academic.departments(department_id)
);

CREATE TABLE academic.courses (
    course_id VARCHAR(10) PRIMARY KEY,
    course_name VARCHAR(100) NOT NULL,
    department_id INT NOT NULL,
    credits INT CHECK (credits BETWEEN 1 AND 6),
    professor_id INT,
    FOREIGN KEY (department_id) REFERENCES academic.departments(department_id),
    FOREIGN KEY (professor_id) REFERENCES academic.professors(professor_id)
);

CREATE TABLE administration.students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    date_of_birth DATE,
    enrollment_date DATE DEFAULT CURRENT_DATE,
    major_department_id INT,
    FOREIGN KEY (major_department_id) REFERENCES academic.departments(department_id)
);

CREATE TABLE academic.enrollments (
    student_id INT,
    course_id VARCHAR(10),
    semester VARCHAR(20) NOT NULL,
    grade CHAR(2),
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (student_id, course_id, semester),
    FOREIGN KEY (student_id) REFERENCES administration.students(student_id),
    FOREIGN KEY (course_id) REFERENCES academic.courses(course_id),
    CHECK (grade IN ('A+', 'A', 'A-', 'B+', 'B', 'B-', 'C+', 'C', 'C-', 'D+', 'D', 'F', 'W', 'I'))
);

-- Create indexes
CREATE INDEX idx_student_name ON administration.students(last_name, first_name);
CREATE INDEX idx_course_dept ON academic.courses(department_id);
CREATE INDEX idx_enrollment_date ON academic.enrollments(enrollment_date);

-- Create views
CREATE VIEW academic.department_course_count AS
SELECT d.department_name, COUNT(c.course_id) AS course_count
FROM academic.departments d
LEFT JOIN academic.courses c ON d.department_id = c.department_id
GROUP BY d.department_name;

CREATE VIEW administration.student_transcript AS
SELECT s.student_id, s.first_name, s.last_name, 
       c.course_id, c.course_name, e.semester, e.grade
FROM administration.students s
JOIN academic.enrollments e ON s.student_id = e.student_id
JOIN academic.courses c ON e.course_id = c.course_id
ORDER BY s.student_id, e.semester;

Modifying an Existing Database Structure

-- Add new columns
ALTER TABLE administration.students ADD COLUMN email VARCHAR(100);
ALTER TABLE administration.students ADD COLUMN graduation_year INT;

-- Add constraints to new columns
ALTER TABLE administration.students ADD CONSTRAINT uq_student_email UNIQUE (email);
ALTER TABLE administration.students ADD CONSTRAINT chk_grad_year CHECK (graduation_year >= 2000);

-- Modify existing columns
ALTER TABLE academic.professors MODIFY COLUMN salary DECIMAL(12,2);

-- Rename columns
ALTER TABLE academic.departments RENAME COLUMN building TO location;

-- Add new table
CREATE TABLE administration.alumni (
    alumni_id INT PRIMARY KEY,
    student_id INT NOT NULL,
    graduation_date DATE NOT NULL,
    current_employer VARCHAR(100),
    FOREIGN KEY (student_id) REFERENCES administration.students(student_id)
);

-- Create a new index
CREATE INDEX idx_alumni_grad_date ON administration.alumni(graduation_date);

-- Modify an existing view
ALTER VIEW administration.student_transcript AS
SELECT s.student_id, s.first_name, s.last_name, s.email,
       c.course_id, c.course_name, c.credits, e.semester, e.grade
FROM administration.students s
JOIN academic.enrollments e ON s.student_id = e.student_id
JOIN academic.courses c ON e.course_id = c.course_id
ORDER BY s.student_id, e.semester;

DDL provides the foundation for database structure design and management. Understanding DDL commands and their proper use is essential for database administrators and developers to create robust, efficient, and maintainable database systems.