DDL in DBMS
FREE
Overview
SQL(Structured Query Language) commands are used to create and maintain Databases. Data Definition Language(DDL) is a type of SQL command used to define the components of databases using Database Management Systems.
DDL in DBMS is used to create or modify the database objects like tables, views etc. These commands deal with defining a schema and adding a table description to it.
DDL commands define the table structure and its column properties. While DML(Data Manipulation Language) commands in SQL are used to manipulate the data in the tables. DML commands add, delete and update data in the table using commands like INSERT, UPDATE, and DELETE in SQL.
What is a DDL ?
Structured Query Language(SQL) is a standard language used to create, access, and manipulate databases, being an integral part of Database Management Systems(DBMS). SQL uses various commands to manipulate the database and these commands are divided into 5 parts as follows :
- DDL :
Data Definition Language(DDL) is used to define database schema in DBMS. - DML :
Data Manipulation Language(DML) is used to manipulate data present in the database. - DCL :
Data Control Language(DCL) deals with access rights and data control on the data present in the database. - TCL :
Transaction Control Language(TCL) deals with the transactions happening in the database. - DQL :
Data Query Language(DQL) is used to retrieve data from the database using SQL queries.
Data Definition Language(DDL) commands in DBMS are used to define the database objects. We can create, delete and alter tables using DDL commands. Since a database structure is designed by DDL commands, they are called Data Definition Languages (DDLs). They consist of commands such as CREATE, ALTER, TRUNCATE, and DROP.
When any data is being stored in a database, we need to create tables and define their structure DDL commands are used to do the same.
For example, if a school wants to store its data in a database then we need to create tables like students and teachers. Furthermore, we need to define the structure of tables, for instance, the student's table will have columns like their roll no, name, age, date of birth, address, etc.
DDL Commands
- DDL in DBMS is a language that allows user to define the database components and their relationship with each other.
- These commands work with the structure of tables like creating a table, deleting a table, and altering a table.
- DDL commands allow us to define and change the data types of table columns, and integrity constraints while defining the table structure. Integrity constraints are rules set for the data being stored in the table. This is explained in the next section of this article.
- All DDL commands are auto-committed which means the changes done using them are permanently saved in the Database but they can be rolled back.
The DDL commands in DBMS are as follows :
- CREATE
- ALTER
- TRUNCATE
- DROP
Create
CREATE is a DDL command in DBMS that is used to create tables or databases. While creating a table, we specify table names, column names, datatypes of each column, and column sizes. If there are any integrity constraints or key constraints of the table like PRIMARY KEY, UNIQUE, NOT NULL, etc. we must specify them while creating it along with the column names and details.
Notes :
-
Some of the essential datatypes in SQL are as follows :
- int/number :
Stores integer numbers - varchar :
Stores string - date :
Stores date
- int/number :
-
Integrity constraints are the rules that a table's data columns must follow.
Some of the integrity constraints in SQL are as follows :
- PRIMARY KEY :
Primary key is a column that is unique in the table and is used to uniquely identify each record in the table. - FOREIGN KEY :
Foreign key is a column used to define a relationship between two tables by referencing to Primary key of another table. - NOT NULL :
Not null integrity constraint is used to say that this column cannot be null. It has to have some data.
- PRIMARY KEY :
-
SQL commands are , not case-sensitive.
Syntax :
Example :
Let's create a table that store data of school students using CREATE DDL command.
Output :
We have created a new table called , Students, using the DDL command CREATE. There are 4 columns in the table, which are specified in the command with their corresponding data types and sizes. The PRIMARY KEY of this table is Roll_no, and it has been specified when defining the table with the PRIMARY KEY integrity constraint. In the output section, a blank table Students is displayed that was created using the CREATE command with the specified columns. The table is empty as we have not added any data to it yet.
Alter
ALTER is a DDL command used to change the structure of an existing database table. Using this command we can perform operations like adding a new column, removing any column, adding or removing integrity constraints, or changing the data type of the existing column on the existing table. ALTER command is used to modify the existing structure of the table.
Syntax :
Example - 1 :
Let's add a new column in the Students table using the ALTER DDL command.
Output :
In this section, we have modified the existing table Students using the ALTER command in DBMS. We have added a new column named Date_of_Birth with data type DATE using the ALTER DDL command in DBMS. In the output section, we can see the column has been added to the existing table.
Example - 2 :
Let's see one more example illustrating how to modify an existing column in the table.
In this command ALTER statement is used to change the data type of the existing column Age in the Students table from int to varchar
Truncate
TRUNCATE is a DDL command used to delete all records from the table while keeping its structure intact. This command is used to get rid of all the records and free up the space being used by the table. However, the table's structure remains unchanged, including the column names and details. Thus, the table can be used to store new data in the future.
Syntax :
Example :
As shown in the image below, the Students table stores records of some students currently.
Consider, that the school has started a new batch of students and wants to store their details in the same structure. They can use the same table by removing the records from the existing table and keeping its structure as it is. Let's achieve the same using the TRUNCATE DDL commands in DBMS.
Output :
As we can see in the output, after using the TRUNCATE command all the records from the Students table are deleted and the table structure is as it is. The same empty Students can be used again.
Drop
DROP is a DDL command used to delete an existing table completely from the database. This command deletes the table records along with their structure. The table and all its components are completely deleted and cannot be found again in the database.
Syntax :
Example :
Let's delete the Students table completely from the database.
The above DROP command deletes the Students table from the database.
Conclusion
- Data Definition Language is a subset of SQL used to define the structure of the database.
- All DDL commands are auto-committed i.e. all the changes are saved permanently in the database.
- The structure of the database is defined using these commands hence they are known as Data Definition Language in DBMS.
- CREATE, ALTER, TRUNCATE, and DROP are some of the common DDL commands in DBMS.