What is Data Definition Language (DDL)?

Simply put, DDL is a subset of SQL commands that we use to create and modify the structure of objects in a database. We can change the database structure by adding new tables or objects with all of their attributes such as data type, table name, and so on.

To gain a better understanding, let's delve deeper into each DDL command shown in the diagram. To demonstrate these commands, let's pretend we're working on a database for a fictitious music store Sequel Records.

CREATE

The DDL command CREATE is used to create the database or any objects such as a table, index, function, view, and store procedures.

1. CREATE DATABASE

Firstly we must create the database for our store. The CREATE query below allows you to create a new database.

1CREATE DATABASE musicstore;

2. CREATE TABLE

We firstly want to create a table containing all of the information on the albums we sell. Within the CREATE statement, we must first define the structure of our table, including column names, data types, and the size of the data to be added to each column.

1CREATE TABLE albums (
2    album_id INT,
3    title VARCHAR(150),
4    artist VARCHAR (80),
5    year_published SMALLINT,
6    price DECIMAL (6,2) NOT NULL,
7    genre VARCHAR(20) NOT NULL,
8    PRIMARY KEY (album_id));

From there we can use Data Manipulation Language (DML) specifically the command INSERT to populate the table with the data from our albums.

3. CREATE VIEW

Suppose we run a query and want to save the results-set as a table, in this situation we can create a view using the CREATE command. Because a view displays current data, the database engine recreates it each time a user queries it.

1CREATE VIEW DE_view AS
2SELECT album_id, title, artist, price
3FROM albums
4WHERE genre = ‘Dance/Electronic’;

We can then run queries on our view as if it was a separate table.

1SELECT title, artist
2FROM DE_view;

4. CREATE INDEX

Using the CREATE command we can create indexes for our table. Indexes are used to retrieve data from a database more quickly than would otherwise be possible. The indexes are not visible to users; they are only used to speed up searches and queries.

As an example, our database becomes increasingly populated with albums over time, to the point where searching becomes extremely slow. Using the following command, we create an index on a single column title or more than one column title and artist.

1-- One column index
2CREATE INDEX index_album
3ON albums (title);
4
5-- Multiple column index
6CREATE INDEX index_album_m
7ON albums (title, artist);

In addition, we can make indexes unique in order to maintain data integrity by ensuring that no two rows of data in a table have the same key value.

1CREATE UNIQUE INDEX index_album_u
2ON albums (title);

5. CREATE PROCEDURE

The CREATE command can also be used to create a stored procedure which is basically a prepared SQL code that can be saved and reused over and over again.

1DELIMITER //
2CREATE PROCEDURE SelectAllAlbums()
3BEGIN
4	SELECT * FROM albums;
5END //
6DELIMITER ;

To execute our stored procedure we simply run the command below. Handy right?

1CALL SelectAllAlbums();

ALTER

1. ALTER TABLE

The DDL command ALTER is used to change the structure of the databases we create. For example, you might want to be able to keep track of inventory with your new database. You decide to add a column called qty_on hand.

1ALTER TABLE albums
2ADD qty_on_hand SMALLINT NOT NULL;

Or you notice that the data type you entered for album_id is an integer, when you actually wanted it to be a VARCHAR of length 3.

1ALTER TABLE albums
2MODIFY COLUMN album_id VARCHAR(3);

DROP

Using the DROP command we can delete the database or any objects associated with it.

1. DROP DATABASE

Using the following command we can delete our entire database. Before running this command, you should be aware that it will delete your entire database including all tables, data, indexes, and other elements, so make sure you're certain before proceeding.

1DROP DATABASE musicstore;

2. DROP TABLE

Similar to dropping a database, when we drop a table everything will be deleted.

1DROP TABLE albums;

3. DROP VIEW

And to drop a view.

1DROP VIEW DE_view;

3. DROP COLUMN

Let's say we want to delete a specific column. In this case we use the ALTER TABLE command in conjunction with the DROP command. As the owner of the music store, you determine that the column year_published is no longer necessary in this table and decide to remove it.

1ALTER TABLE albums
2DROP COLUMN year_published;

4. DROP CONSTRAINT

Another useful feature of this command is the ability to remove constraints. Below we will go through the syntax for removing all constraints with the command DROP.

1-- Drop the Primary Key
2ALTER TABLE albums
3DROP PRIMARY KEY;
4
5-- Drop the Foreign Key
6ALTER TABLE albums
7DROP FOREIGN KEY FK_AlbumOrder;
8
9-- Drop a Unique constraint 
10ALTER TABLE albums
11DROP INDEX UC_Album;
12
13-- Drop the Check constraint
14ALTER TABLE albums
15DROP CONTRAINT CHK_AlbumPrice;
16
17-- Drop the Default constraint
18ALTER TABLE abums
19ALTER genre DROP DEFAULT;

5. DROP INDEX

Imagine we no longer have as much albums in our table therefore there is no real need for the index we created in the first part of this tutorial. We can remove the index using the DROP command.

1ALTER TABLE album
2DROP INDEX index_album;  

TRUNCATE

The TRUNCATE TABLE command deletes the data within a table. Unlike DROP, it keeps the table's structure, indexes, and dependencies. If we wanted to remove all data entries from a table in a database while keeping the table albums structure but not the entries, we would use the SQL command below.

1TRUNCATE TABLE albums;  
2
3-- Check table with a query
4SELECT * FROM albums;

RENAME

The DDL command RENAME, as expected is used to change names in our database.

1. RENAME TABLE

Perhaps we should choose a more appropriate name for our table in our music store. We can do so with the following.

1ALTER TABLE albums
2RENAME TO our_albums;  

2. RENAME COLUMN

Or maybe we want to rename a column, we can do the following.

1ALTER TABLE albums
2RENAME COLUMN title TO album_name;  

One Pager Cheat Sheet

• Data Definition Language (DDL) is a subset of SQL commands that we use to create, modify and manipulate the structure of database objects.
• The CREATE command is used to create objects such as a database, table, index, view, and stored procedure in a database.
• The CREATE TABLE command is used to create a new table called artists in the database, defining its columns, data types, size, and PRIMARY KEY.
• The DDL command ALTER is used to modify existing databases by adding or modifying columns in a table.
• We can use the ALTER command with the ADD keyword to add a new column called nationality to the table artists.
• Using the DROP command we can delete a database, table, column, constraint, or index.
• The DROP TABLE command is used to delete tables, such as artists, followed by a semicolon.
• The TRUNCATE TABLE command is used to delete data entries from a table while keeping its structure, indexes, and dependencies.
• The TRUNCATE TABLE command deletes the data within a table but does not affect its structure, indexes or dependencies.
• The DDL command RENAME can be used to change the name of tables and columns in our database.
• We RENAME the column year_published to release_year in the albums table using the RENAME COLUMN clause of the ALTER TABLE DLL command.