3.3 Data Definition Language (DDL)
Data Definition Language (DDL) is a subset of SQL (Structured Query Language) used to define, manage, and modify the structure of database objects such as tables, indexes, and schemas. The key DDL commands are CREATE, ALTER, DROP, and TRUNCATE.
Key DDL Commands
1. CREATE: Used to create new database objects.
2. ALTER: Used to modify the structure of existing database objects.
3. DROP: Used to delete database objects.
4. TRUNCATE: Used to remove all records from a table, but it does not remove the table itself.
Sample Examples of DDL Commands
1. CREATE Command
The CREATE command is used to create new tables, databases, indexes, or other objects.
Example: Creating a Table
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
hire_date DATE,
department_id INT
);
Explanation: This command creates a new table named employees with columns employee_id, first_name, last_name, hire_date, and department_id. The employee_id column is the primary key, and first_name and last_name are mandatory fields (NOT NULL).
2. ALTER Command
The ALTER command is used to modify the structure of an existing table, such as adding, deleting, or modifying columns.
Example: Adding a New Column
ALTER TABLE employees
ADD email VARCHAR(100);
Explanation: This command adds a new column named email to the employees table.
Example: Modifying a Column
ALTER TABLE employees
MODIFY COLUMN first_name VARCHAR(100);
Explanation: This command modifies the first_name column to allow up to 100 characters.
3. DROP Command
The DROP command is used to remove an existing database object like a table or database.
Example: Dropping a Table
DROP TABLE employees;
Example: Dropping a Database
DROP DATABASE company_db;
4. TRUNCATE Command
The TRUNCATE command is used to remove all records from a table, but it does not remove the table structure itself.
Example: Truncating a Table
TRUNCATE TABLE employees;
Differences Between TRUNCATE and DELETE
TRUNCATE: Removes all records from a table and resets any auto-increment counters. It is faster and uses fewer resources than DELETE.
DELETE: Removes records row by row and can be used with a WHERE clause to delete specific records. It is slower than TRUNCATE for large datasets.
Example: Combining DDL Operations
Creating a New Table and Adding Constraints
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(50) NOT NULL,
location VARCHAR(100),
budget DECIMAL(15, 2) CHECK (budget >= 0)
);
Explanation: This example creates a departments table with columns department_id, department_name, location, and budget. The budget column has a CHECK constraint ensuring that the budget cannot be negative.
Summary
DDL commands are essential for defining and managing the structure of database objects. Key commands include CREATE, ALTER, DROP, and TRUNCATE. DDL operations are typically used by database administrators or developers to design and modify databases and their components.