Data Definition Language (DDL)

Data Definition Language (DDL): Data Definition Language (DDL) is a subset of SQL (Structured Query Language). It is used to define and manage the structure of a database. It focuses on creating, modifying, and deleting database objects like tables, views, indexes, schemas, and other related structures. DDL commands are essential for database design and structure management. They guarantee that the data is organized for efficient storage and retrieval.

Thank you for reading this post, don't forget to subscribe!

In this article we divide into the core aspects of DDL, explaining its purpose, commands, syntax, and examples.

What is Data Definition Language (DDL)?

DDL is primarily responsible for defining the database schema and its objects. Unlike DML (Data Manipulation Language), which deals with data, DDL operates on the structure of the database. The changes made using DDL commands are automatically committed, meaning they cannot be rolled back unless a proper backup exists.

Key Features of DDL:

• Schema Definition: DDL commands define and manage the structure of a database.
• Auto-Commit: Changes made using DDL are instantly saved.
• Structure-Oriented: Focuses on objects rather than data manipulation.
• Integrity: Ensures the consistency and integrity of database objects.

DDL Commands and Syntax:

Below are the most commonly used DDL commands along with their syntax and purpose:

1. CREATE

The CREATE command creates new database objects like tables, views, or schema.

Syntax

CREATE TABLE table_name (
    column1 datatype [constraints],
    column2 datatype [constraints],
    ...
);

Example

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    department VARCHAR(30),
    joining_date DATE
);

Above example creates a table named employees with four columns: employee_id, name, age, and department.

2. ALTER:

The ALTER command modifies the structure of an existing database object. You can add, delete, or modify columns.

Syntax

ALTER TABLE table_name
ADD column_name datatype;

ALTER TABLE table_name
DROP COLUMN column_name;

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:

ALTER TABLE employees
ADD salary DECIMAL(10, 2);

This example adds a new column salary to the employee's table.

3. DROP

The DROP command is used to delete database objects like tables, views, or indexes.

Syntax

DROP TABLE table_name;
DROP VIEW view_name;

Example

DROP TABLE employees;

This example deletes the employees table along with all its data and structure.

4. TRUNCATE

The TRUNCATE command removes all records from a table without affecting its structure. It is faster than DELETE as it doesn’t log individual row deletions.

Syntax

TRUNCATE TABLE table_name;

Example:

TRUNCATE TABLE employees;

5. RENAME

The RENAME command changes the name of a database object.

Syntax:

RENAME old_table_name TO new_table_name;

Example:

RENAME employees TO staff;

Above example renames the employees table to staff.

6. COMMENT

The COMMENT command adds descriptive information to a database object.

Syntax:

COMMENT ON TABLE table_name IS 'description';
COMMENT ON COLUMN table_name.column_name IS 'description';

Example:

COMMENT ON TABLE employees IS 'Table containing employee details';

Key Considerations When Using DDL

1. Impact of Auto-Commit: DDL commands automatically save changes, so always double-check before executing them.
2. Backup: It is recommended to back up critical data before altering or dropping objects.
3. Permissions: Ensure you have the necessary privileges to execute DDL commands.