What is Data Definition Language (DDL)

What is Data Definition Language (DDL)

03 Jul 2024
Beginner
96 Views
9 min read

Data Definition Language (DDL)

DDL, which stands for Data Definition Language is a component of SQL (Structured Query Language). It is utilized in managing database systems (DBMS) to define and oversee all the elements, within a database. DDL instructions are employed for establishing, altering, and removing database configurations without affecting the data. Through the application of DDL commands database frameworks can be, Upheld, guaranteeing the arrangement and format of information stored in the database.

In this DBMS Tutorial, We will learn deep down about Data Definition Language which will include Key DDL Commands, Creating Databases, Creating Tables, etc.

DDL Commands

These SQL commands are used for creating a table, deleting a table, altering a table, and truncating the table. All the commands of DDL are auto-committed i.e. it permanently saves all the changes in the database.
In this category, we have four main commands as shown in below figure.
DDL Commands

Basics of SQL Commands

1. CREATE

Here, The CREATE command is especially used to create new database objects such as databases, tables, indexes, and views.

Syntax

The syntax of the CREATE command is as follows:
CREATE TABLE table_name
(
column_1 datatype,
column_2 datatype,
column_3 datatype,
....
);

Example

Here We can create a table for storing Student information as follows.

CREATE TABLE Students(
    stud_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    admission_date DATE
);

2. ALTER

The ALTER command is used to modify existing database objects, such as adding, deleting, or modifying columns in a table.

Syntax

The syntax of the ALTER command is as follows:
ALTER TABLE table_name
ADD column_name datatype;

Example

ALTER TABLE students ADD COLUMN email VARCHAR(100);

3. DROP

The DROP command is used to delete existing database objects permanently

Syntax

The syntax of the DROP command is as follows:
DROP TABLE table_name;

Example

DROP TABLE students;

4. TRUNCATE

The TRUNCATE command removes all rows from a table without deleting the table itself. It resets any auto-incremented columns.

Syntax

TRUNCATE TABLE table_name;

Example

TRUNCATE TABLE employees;

Best Practices using DDL

Creating Database Objects

1. Creating Databases

Creating a new database involves defining the database name and establishing its initial structure.
CREATE DATABASE company_db;

2. Creating Tables

Creating tables involves defining the table's columns, data types, and constraints.
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(100)
);

3. Creating Indexes

Indexes are created to enhance the performance of data retrieval operations.
CREATE INDEX idx_employee_name ON employees (last_name);

Modifying Database

1. Objects Altering Tables

Altering tables allows for adding, dropping, or modifying columns and constraints.
ALTER TABLE employees MODIFY COLUMN email VARCHAR(255);

2. Renaming Database

Objects Renaming database objects involves changing the name of existing objects.
ALTER TABLE employees RENAME TO staff;

Deleting Database

1. Dropping Tables and Databases

Dropping tables and databases remove the objects and their data permanently.
DROP DATABASE company_db;
DROP TABLE departments;

2. Truncating Tables

Truncating tables removes all data from a table quickly without affecting the table structure.
TRUNCATE TABLE departments;

Constraints in DDL

Types of Constraints are rules enforced on data columns to maintain data integrity.

1. PRIMARY KEY

  • PRIMARY KEY ensures unique identification for each row.

2. FOREIGN KEY

  • FOREIGN KEY Maintains referential integrity between tables.
Differences between Primary Key and Foreign Key

3. UNIQUE KEY

  • It ensures all values in a column are unique.
Difference between Primary Key and Unique Key

4. NOT NULL

  • NOT NULL ensures a column cannot have NULL values.

5. CHECK

  • CHECK constraints ensure that all values in a column satisfy a specific condition.

6. ALTER TABLE Command

  • Adding and Removing Constraints Constraints can be added or removed using the ALTER TABLE command.
    ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
    ALTER TABLE employees DROP CONSTRAINT chk_salary;
Our Courses
SQL Server Course
MongoDB Course
Conclusion
Data Definition Language (DDL) is an essential component of SQL. By understanding and utilizing DDL commands effectively, database administrators and developers can create, modify, and delete database schemas, ensuring organized and efficient data management. We can Adopt best practices and troubleshoot common issues that can further enhance the effectiveness of using DDL in DBMS.
Similar Articles
Different Types of SQL Keys
Introduction to SQL Server
SQL Server Insert, Retrieve, Update, Delete Operations using Stored Procedures
Interview Preparation
DBMS Top 50 Most Important Interview Questions
Top 50 SQL Server Interview Questions and Answers

FAQs

Q1. What is DCL and TCL?

DQL – Data Query Language. 
DML – Data Manipulation Language.
DCL – Data Control Language. TCL – Transaction Control Language.

Q2. What are DML commands?

DML is an abbreviation for Data Manipulation Language. Represents a collection of programming languages explicitly used to make changes to the database, such as: CRUD operations to create, read, update and delete data. Using INSERT, SELECT, UPDATE, and DELETE commands

Q3. What is DDL and its functions?

A Data Definition Language (DDL) refers to a language that is used to modify data and define data structures.
Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
ASP.NET Core Certification Training Jul 17 MON, WED, FRI
Filling Fast
07:00AM to 08:30AM (IST)
Get Details
Advanced Full-Stack .NET Developer Certification Training Jul 17 MON, WED, FRI
Filling Fast
07:00AM to 08:30AM (IST)
Get Details
Angular Certification Course Jul 20 SAT, SUN
Filling Fast
06:00PM to 08:00PM (IST)
Get Details
Generative AI For Software Developers Jul 20 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
Azure Master Class Jul 20 SAT, SUN
Filling Fast
03:00PM to 05:00PM (IST)
Get Details
ASP.NET Core Certification Training Jul 28 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
Software Architecture and Design Training Jul 28 SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
.NET Solution Architect Certification Training Jul 28 SAT, SUN
Filling Fast
05:30PM to 07:30PM (IST)
Get Details
Azure Developer Certification Training Jul 28 SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details
Advanced Full-Stack .NET Developer Certification Training Jul 28 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details
Data Structures and Algorithms Training with C# Jul 28 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details
Angular Certification Course Aug 11 SAT, SUN
Filling Fast
09:30AM to 11:30AM (IST)
Get Details
ASP.NET Core Project Aug 24 SAT, SUN
Filling Fast
07:00AM to 09:00AM (IST)
Get Details

Can't find convenient schedule? Let us know

About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Self-paced Membership
  • 22+ Video Courses
  • 800+ Hands-On Labs
  • 400+ Quick Notes
  • 55+ Skill Tests
  • 45+ Interview Q&A Courses
  • 10+ Real-world Projects
  • Career Coaching Sessions
  • Email Support
Upto 60% OFF
Know More
Accept cookies & close this