Last modified: December 12, 2018
This article is written in: 🇺🇸
Welcome to the world of Data Definition Language, or DDL for short. If you've ever wondered how databases are structured and how those structures are created and modified, you're in the right place. DDL is a subset of SQL (Structured Query Language) that focuses on defining and managing the schema of a database. Think of it as the blueprint for your database, where you lay out the design of tables, indexes, views, and other elements that store and organize your data.
Imagine building a house—you need a plan that outlines where the rooms will be, how big they are, and how they're connected. Similarly, DDL provides the commands to create the "rooms" (tables) in your database, specify their "furniture" (columns and data types), and decide how everything is connected. It allows you to:
By mastering DDL, you gain control over the foundational aspects of your database, ensuring that your data is organized efficiently and effectively.
Let's dive into the main commands that make up DDL and see how they help you shape your database.
CREATE TABLEThe CREATE TABLE statement is your starting point for adding new tables to your database. It defines the table's name, the columns it contains, their data types, and any constraints that enforce data integrity.
Example:
Suppose you want to create a table to store information about employees. Here's how you might do it:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
date_of_birth DATE,
department_id INT
);In this command, we're creating a table named employees with the following columns:
employee_id is an integer field that uniquely identifies each employee and is set as the primary key to ensure uniqueness. first_name and last_name are text fields designed to store the employee's names, each with a character limit of 50. date_of_birth is a date field used to record the birth date of the employee for reference. department_id is an integer field that links the employee to a specific department, creating an association. Visualizing the Table Structure:
+-------------+------------+-----------+---------------+---------------+
| employee_id | first_name | last_name | date_of_birth | department_id |
+-------------+------------+-----------+---------------+---------------+
| | | | | |
+-------------+------------+-----------+---------------+---------------+At this point, the table is empty, but it's ready to hold employee data.
ALTER TABLEAs your database evolves, you might need to change the structure of your tables. The ALTER TABLE statement allows you to add, modify, or remove columns and constraints.
Example:
Let's say you realize you need to store each employee's email address. You can add a new column to the employees table like this:
ALTER TABLE employees
ADD email VARCHAR(100);Now, the employees table includes the email column:
+-------------+------------+-----------+---------------+---------------+-------------------+
| employee_id | first_name | last_name | date_of_birth | department_id | email |
+-------------+------------+-----------+---------------+---------------+-------------------+
| | | | | | |
+-------------+------------+-----------+---------------+---------------+-------------------+This new column can store up to 100 characters, accommodating most email addresses.
DROP TABLEWhen a table is no longer needed, you can remove it entirely using the DROP TABLE statement. This action deletes the table and all of its data permanently.
Example:
Suppose the employees table is obsolete, and you want to remove it:
DROP TABLE employees;After executing this command, the employees table no longer exists in your database. Be cautious with DROP TABLE, as this action cannot be undone.
TRUNCATE TABLEIf you want to remove all data from a table but keep its structure for future use, TRUNCATE TABLE comes in handy. It's a quick way to delete all rows without dropping the table itself.
Example:
To empty the employees table:
TRUNCATE TABLE employees;Before truncation, the table might look like this:
+-------------+------------+-----------+---------------+---------------+
| employee_id | first_name | last_name | date_of_birth | department_id |
+-------------+------------+-----------+---------------+---------------+
| 1 | Alice | Smith | 1985-04-12 | 101 |
| 2 | Bob | Johnson | 1990-07-23 | 102 |
+-------------+------------+-----------+---------------+---------------+After truncation, all the data is gone, but the table remains:
+-------------+------------+-----------+---------------+---------------+
| employee_id | first_name | last_name | date_of_birth | department_id |
+-------------+------------+-----------+---------------+---------------+
| | | | | |
+-------------+------------+-----------+---------------+---------------+This is useful when you need to reset a table's data without affecting its structure or relationships.
RENAME TABLE or ALTER TABLEChanging the name of a table can be necessary when its purpose evolves or to adhere to new naming conventions. Depending on your database system, you can use RENAME TABLE or ALTER TABLE to accomplish this.
Example (Using ALTER TABLE in PostgreSQL):
ALTER TABLE employees RENAME TO staff;Example (Using RENAME TABLE in MySQL):
RENAME TABLE employees TO staff;After renaming, the table previously known as employees is now called staff:
+-------------+------------+-----------+---------------+---------------+
| employee_id | first_name | last_name | date_of_birth | department_id |
+-------------+------------+-----------+---------------+---------------+
| | | | | |
+-------------+------------+-----------+---------------+---------------+This change updates the table's name in the database schema, but the structure and data remain the same.
To deepen our understanding, let's explore some practical scenarios involving DDL statements and interpret the outcomes.
Suppose we want to create a new table to store information about departments within a company:
CREATE TABLE departments (
department_id INT PRIMARY KEY,
department_name VARCHAR(100),
manager_id INT
);This command sets up a departments table with:
department_id serves as a unique identifier for each department, ensuring distinct identification in the database. department_name represents the name assigned to each department for identification and reference. manager_id corresponds to the ID of the manager responsible for overseeing the department's operations. Table Structure:
+---------------+------------------+------------+
| department_id | department_name | manager_id |
+---------------+------------------+------------+
| | | |
+---------------+------------------+------------+Later, we decide to add the location of each department:
ALTER TABLE departments
ADD location VARCHAR(50);The departments table now includes a location column:
+---------------+------------------+------------+----------+
| department_id | department_name | manager_id | location |
+---------------+------------------+------------+----------+
| | | | |
+---------------+------------------+------------+----------+Perhaps the manager_id column needs a clearer name. We can rename it to head_id:
For MySQL:
ALTER TABLE departments
CHANGE manager_id head_id INT;For PostgreSQL:
ALTER TABLE departments
RENAME COLUMN manager_id TO head_id;Now, the column reflects its new name:
+---------------+------------------+----------+----------+
| department_id | department_name | head_id | location |
+---------------+------------------+----------+----------+
| | | | |
+---------------+------------------+----------+----------+If the location information is no longer needed, we can remove that column:
ALTER TABLE departments
DROP COLUMN location;The departments table reverts to:
+---------------+------------------+----------+
| department_id | department_name | head_id |
+---------------+------------------+----------+
| | | |
+---------------+------------------+----------+If we decide to remove the departments table entirely:
DROP TABLE departments;The table and all its data are permanently deleted from the database.
DDL not only defines the structure of tables but also allows you to enforce rules to maintain data integrity. Constraints are conditions that the data must satisfy, ensuring accuracy and consistency.
When creating a table, you can define constraints directly:
CREATE TABLE projects (
project_id INT PRIMARY KEY,
project_name VARCHAR(100) NOT NULL,
start_date DATE,
end_date DATE,
budget DECIMAL(10, 2) CHECK (budget > 0),
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);This projects table includes:
PRIMARY KEY on project_id.NOT NULL constraint on project_name to ensure every project has a name.CHECK constraint on budget to ensure it's a positive number.FOREIGN KEY linking manager_id to the employees table.You can also add constraints to an existing table:
ALTER TABLE employees
ADD CONSTRAINT fk_department
FOREIGN KEY (department_id)
REFERENCES departments(department_id);This adds a foreign key constraint to the employees table, linking department_id to the departments table.
Beyond tables, DDL commands also help you create and manage indexes and views, enhancing database performance and usability.
Indexes improve query performance by allowing the database to find data faster.
CREATE INDEX idx_last_name ON employees(last_name);This command creates an index on the last_name column of the employees table, speeding up searches based on last names.
If an index is no longer necessary, you can remove it:
DROP INDEX idx_last_name;This deletes the index, potentially slowing down queries that relied on it but freeing up system resources.
A view is a virtual table based on a SELECT query. It simplifies complex queries and enhances security by restricting access to specific data.
CREATE VIEW employee_overview AS
SELECT employee_id, first_name, last_name, department_name
FROM employees
JOIN departments ON employees.department_id = departments.department_id;This view provides a simplified way to see employee information alongside their department names.
To remove a view:
DROP VIEW employee_overview;The view is deleted, but the underlying tables remain unaffected.