What Does Data Definition Language (DDL) Mean?

A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc.

This term is also known as data description language in some contexts, as it describes the fields and records in a database table.

The present database industry incorporates DDL into any formal language describing data. However, it is considered to be a subset of SQL (Structured Query Language). SQL often uses imperative verbs with normal English such as sentences to implement database modifications. Hence, DDL does not show up as a different language in an SQL database, but does define changes in the database schema.

It is used to establish and modify the structure of objects in a database by dealing with descriptions of the database schema. Unlike data manipulation language (DML) commands that are used for data modification purposes, DDL commands are used for altering the database structure such as creating new tables or objects along with all their attributes (data type, table name, etc.).

Commonly used DDL in SQL querying are CREATE, ALTER, DROP, and TRUNCATE.

Truncate

Similar to DROP, the TRUNCATE statement is used to quickly remove all records from a table. However, unlike DROP that completely destroys a table, TRUNCATE preserves its full structure to be reused later.

Truncate statement syntax is:

TRUNCATE TABLE table_name;

For example:

TRUNCATE TABLE Employee;

In this example, we’re marking all the extents of the Employee table for deallocation, so they’re considered empty for reuse.

Drop

A drop command is used to delete objects such as a table, index or view. A DROP statement cannot be rolled back, so once an object is destroyed, there’s no way to recover it.

Drop statement syntax is:

DROP object type object name;

For example:

DROP TABLE Employee;

In this example, we’re deleting the Employee table.

Alter

An alter command modifies an existing database table. This command can add up additional column, drop existing columns and even change the data type of columns involved in a database table.

An alter command syntax is:

ALTER object type object name parameters;

For example:

ALTER TABLE Employee ADD PRIMARY KEY (employee_pk);

In this example, we added a unique primary key to the table to add a constraint and enforce a unique value. The constraint “employee_pk” is a primary key and is on the Employee table.

Create

This command builds a new table and has a predefined syntax. The CREATE statement syntax is:

CREATE TABLE [table name] ([column definitions]) [table parameters];

For example:

CREATE TABLE Employee (Employee Id INTEGER PRIMARY KEY, First name CHAR (50) NULL, Last name CHAR (75) NOT NULL);

The mandatory semi-colon at the end of the statement is used to process every command before it. In this example, the string CHAR is used to specify the data type. Other data types can be DATE, NUMBER, or INTEGER.

WHAT IS SQL

What is Structured Query Language (SQL)?

Structured Query Language (SQL) is a standardized programming language that is used to manage relational database and perform various operations on the data in them. Initially created in the 1970s, SQL is regularly used not only by database administrators, but also by developers writing data integration scripts and data analysts looking to set up and run analytical queries.

The term SQL is pronounced ess-kew-ell or sequel.

SQL is used for the following:

• modifying database table and index structures;
• adding, updating and deleting rows of data; and
• retrieving subsets of information from within relational database management systems (RDBMSes) — this information can be used for transaction processing, analytics applications and other applications that require communicating with a relational database.

SQL queries and other operations take the form of commands written as statements and are aggregated into programs that enable users to add, modify or retrieve data from database tables.

A table is the most basic unit of a database and consists of rows and columns of data. A single table holds records, and each record is stored in a row of the table. Tables are the most used type of database objects, or structures that hold or reference data in a relational database. Other types of database objects include the following:

• Views are logical representations of data assembled from one or more database tables.
• Indexes are lookup tables that help speed up database lookup functions.
• Reports consist of data retrieved from one or more tables, usually a subset of that data that is selected based on search criteria.

Each column in a table corresponds to a category of data — for example, customer name or address — while each row contains a data value for the intersecting column.

Relational databases are relational because they are composed of tables that relate to each other. For example, a SQL database used for customer service can have one table for customer names and addresses and other tables that hold information about specific purchases, product codes and customer contacts. A table used to track customer contacts usually uses a unique customer identifier called a key or  primary key to reference the customer’s record in a separate table used to store customer data, such as name and contact information.

SQL became the de facto standard programming language for relational databases after they emerged in the late 1970s and early 1980s.

DDL COMMAND IN SQL AND HOW TO USE IT

SQL commands broadly fit into four categories:

• DDL (Data Definition Language)
• DML (Data Manipulation Language)
• DCL (Data Control Language)
• TCL (Transactional Control Language)

This article only covers the SQL DDL commands.

SQL DDL commands

The DDL commands in SQL are used to create database schema and to define the type and structure of the data that will be stored in a database. SQL DDL commands are further divided into the following major categories:

• CREATE
• ALTER
• DROP
• TRUNCATE

CREATE

The CREATE query is used to create a database or objects such as tables, views, stored procedures, etc.

Creating a database

The following example demonstrates how the CREATE query can be used to create a database in MS SQL Server:

The script above creates a database named “LibraryDB” in MS SQL Server.

Creating a table

The CREATE query is also used to add tables in an existing database as shown in the following script:

The above script creates a table named “Books” in the “LibraryDB” database that we created earlier.

The “Books” table contains three columns: Id, Name, and Price. The Id column is the primary key column and it cannot be NULL. A column with a PRIMARY KEY constraint must contain unique values. However, since we have set the IDENTITY property for the Id column, every time a new record is added in the Books table, the value of the Id column will be incremented by 1, starting from 1. You need to specify the values for the Name column as well as it cannot have NULL. Finally, the Price column can have NULL values.

To view all the tables in the LibraryDB, execute the following QL DDL script:

You should see the following output:

Similarly, to see all the columns in the Books table, run the following script:

Here is the output:

You can see how the CREATE query can be used to define the structure of a table and the type of data that will be stored in a table. Note, we have not added any record to the Books table yet as SQL DDL commands are only concerned with the structure of the database and not with the database records. The SQL DML commands are used for inserting, modifying and deleting database records.

ALTER

The ALTER command in SQL DDL is used to modify the structure of an already existing table.

Adding a new column

For example, if we want to add a new column e.g. ISBN to the existing Books table in the LibraryDB database, the ALTER command can be used as follows:

The syntax of the ALTER command is straightforward. The ALTER statement is used followed by the object type and the name of the object, which in this case are TABLE and Books, respectively.

Next, you need to specify the operation that you need to perform, which is ADD in our case. Let’s now again SELECT the columns from the Books table and see if the ISBN column has been added to the Books table:

Here is the result set:

In the output, you can see the newly added ISBN column.

Modifying an existing column

Let’s see another case where ALTER command can be useful. Suppose, instead of adding a new column to a table, you want to modify an existing column. For example, you want to change the data type of the ISBN column from INT to VARCHAR (50). The ALTER query can be used as follows:

You can see that to modify an existing column within a table, we first have to use the ALTER command with the table name and then another ALTER command with the name of the column that is to be modified.

If you again select the column names, you will see the updated data type (VARCHAR) for the ISBN column.

DROP

The DROP command is a type of SQL DDL command, that is used to delete an existing database or an object within a database.

Deleting a database

The following DROP command deletes the LibraryDB database that we created earlier:

Note: If you execute the above command, the LibraryDB database will be deleted. To execute the rest of the queries in this article, you will again need to CREATE the LibraryDB database, along with the Books table.

Deleting a table

The DROP command is a type of SQL DDL command that is used to delete an existing table. For instance, the following command will delete the Books table:

Deleting a column

To delete a column within a database, the DROP query is used in combination with the ALTER query. The ALTER query specifies the table that you want to delete whereas the DROP query specifies the column to delete within the table specified by the ALTER query. Let’s drop the ISBN column from the Books:

TRUNCATE

The TRUNCATE command in SQL DDL is used to remove all the records from a table. Let’s insert a few records in the Books table:

Let’s see if the records have been actually inserted:

Here is the result set:

You can see the three records that we inserted in the Books table.

The TRUNCATE command will remove all the records from the Books table as shown below:

If you again select all the records from the Books table, you will see that the table is empty.

Conclusion

In this article, you saw how to use SQL DDL commands to create a database schema and to define and modify the structure of your database. You saw how to execute SQL DDL commands in MS SQL Server with the help of different examples. Have anything to say about the article? Please feel free to comment.

Drop your comment