SQL Data Definition Language (DDL)

Data Definition Language (DDL) is a subset of SQL that is used to define, alter, and drop database objects. These commands are used to manage the structure of the database, not the data within it. DDL statements are typically executed less frequently than DML statements and are often performed by database administrators.

Core DDL Commands

The primary DDL commands in SQL include:

1. CREATE

The CREATE command is used to create new database objects. These objects can include tables, indexes, views, stored procedures, functions, triggers, and more.

Creating a Table

The most common use of CREATE is to define a new table. This involves specifying the table name, column names, their data types, and any constraints.

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY AUTO_INCREMENT,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Email VARCHAR(100) UNIQUE,
    RegistrationDate DATE
);

Creating an Index

Indexes are used to improve the performance of data retrieval operations.

CREATE INDEX idx_LastName ON Customers (LastName);

Creating a View

A view is a virtual table based on the result-set of an SQL statement.

CREATE VIEW ActiveCustomers AS
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE RegistrationDate >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR);

2. ALTER

The ALTER command is used to modify existing database objects. This includes adding, deleting, or modifying columns, constraints, and other properties of a table.

Adding a Column

ALTER TABLE Customers
ADD COLUMN PhoneNumber VARCHAR(20);

Modifying a Column

ALTER TABLE Customers
MODIFY COLUMN Email VARCHAR(150);

Dropping a Column

ALTER TABLE Customers
DROP COLUMN RegistrationDate;

Adding a Constraint

ALTER TABLE Orders
ADD CONSTRAINT fk_CustomerID
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID);

3. DROP

The DROP command is used to delete database objects. Once an object is dropped, it cannot be recovered without a backup. Use this command with caution.

Dropping a Table

DROP TABLE Customers;

Dropping an Index

DROP INDEX idx_LastName ON Customers;

Dropping a View

DROP VIEW ActiveCustomers;

4. TRUNCATE

The TRUNCATE command is used to remove all records from a table. It is faster than DELETE (without a WHERE clause) because it deallocates the data pages used by the table. However, it is a DDL command and cannot be rolled back in some RDBMS. It typically resets any auto-increment counters as well.

TRUNCATE TABLE Orders;

Common Database Objects Managed by DDL

DDL vs. DML

It's important to distinguish DDL from Data Manipulation Language (DML). While DDL defines the structure of the database, DML is used to manage the data within that structure. DML commands include SELECT, INSERT, UPDATE, and DELETE.