SQL Data Description Language (DDL)

Introduction

There are two main types of SQL statements that are executed within databases as described in What Is SQL. Before you can manipulate data residing in a database using SQL Data Manipulation Language (DML), you have to create the logical structure to store information.

Data Definition Language (DDL) is the portion of SQL that deals with how data should reside in the database at a logical level. Each database has its own set of object types that it allows. Most include tables, indexes, views, store procedures, functions, synonyms, and triggers. Each database has its own syntax for DDL statements and the clauses that can be included. There are some basic key words that you will find in almost every RDBMS.

CREATE ALTER DROP TRUNCATE

You can probably guess the basic purpose of a statement that begins with each of those key words.

Following Along

There are several sites that allow you to practice utilizing SQL. I will be utilizing SQL Fiddle. The syntax I will show was tested using the Oracle 11g R2 version. The SQL Fiddle area "Build Schema" is used for DDL statements. If you want to execute the statements yourself just append each new statement to the list of previous statements in SQL Fiddle and then click on the "Build Schema" button.

The CREATE Statement

The basic building blocks of the Relational Database Management System are tables. I envision a table as a set of rows and columns. The columns represent fields of information. The rows represent records in the table. In following graphic, the persons table has four fields and four records.

You could simply create the table with the following statement:

1
2
3
4
5
6
CREATE TABLE books
( book_id VARCHAR(100),
  book_name VARCHAR(100),
  author_id NUMBER,
  editor_id NUMBER)
;

The problem with this table definition is that it allows rows to be created without concern for if the data makes any sense. Envision our table looking like this:

Your database design should make sure that data inserted into a table is sensible. Let us create a second table called the "persons" table. This time we will add constraints to make sure that data entered into the table will make sense. It makes sense that each entry in the table will be unique person so we give it a PRIMARY KEY. We will also want to make sure to track when the table was last updated and who updated it by making those fields NOT NULL.

1
2
3
4
5
6
7
8
CREATE TABLE persons
( person_id NUMBER NOT NULL PRIMARY KEY,
  name VARCHAR(100),
  birth_date DATE,
  gender VARCHAR(30),
  last_update DATE NOT NULL, 
  updated_by NUMBER NOT NULL
 );

We have looked at just the creation of tables there are many other create type statements. Here are some other examples:

NOTE: The design of sqlfiddle.com does not allow these statements to be executed.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE FUNCTION get_bookcount(author IN NUMBER) 
   RETURN NUMBER 
   IS bookcount NUMBER(10);
   BEGIN 
      SELECT count(1) 
      INTO bookcount 
      FROM books 
      WHERE author_id = author; 
      RETURN(bookcount); 
    END;
/

CREATE INDEX editor_indx ON books(editor_id);

CREATE ROLE book_reader;

CREATE TRIGGER persons_update 
    BEFORE INSERT OR UPDATE ON persons
     FOR EACH ROW
        BEGIN
         :new.last_update := sysdate;
        END;
/

The ALTER Statement

The definition of an object in a database can be changed using the ALTER statement. Example: Add constraints to the "books" table to assure the fields "book_name" and "author_id" contain data.

1
2
ALTER TABLE books MODIFY (book_name NOT NULL);
ALTER TABLE books MODIFY (author_id NOT NULL);

A FOREIGN KEY constraint can be added to the fields "author_id" and "the editor_id" limiting the available values to ones that currently exist in the persons table in the "person_id" field.

1
2
3
4
5
ALTER TABLE books ADD CONSTRAINT fk_author 
        FOREIGN KEY (author_id) REFERENCES persons (person_id);
        
ALTER TABLE books ADD CONSTRAINT fk_editor 
    FOREIGN KEY (editor_id) REFERENCES persons (person_id);

What if we wanted to add a publication date to our books table? Use the 'ALTER' statement to add the field.

1
ALTER TABLE books ADD ( publish_date DATE);

You can alter more than just tables. Here are examples of some other ALTER statements.

NOTE: The design of sqlfiddle.com does not allow these statements to be executed.

1
2
3
4
5
ALTER ROLE book_reader IDENTIFIED BY r2Xe135DEw;

ALTER INDEX editor_indx DISABLE;

ALTER TRIGGER persons_update RENAME TO persons_trig;

The TRUNCATE Statement

1
TRUNCATE TABLE books;

The TRUNCATE statement removes all the data from a table. This is very similar to DML statement.

1
DELETE FROM books;

In the Oracle database, there is a difference between the two. TRUNCATE removes all data where a DELETE can be specific in the rows it wants to delete. Also, if you make a mistake with a DELETE statement you can use the transactional control statement ROLLBACK to remove the changes. The TRUNCATE command has no rollback capability. The biggest positive to using the TRUNCATE statement is that it can be faster than the DELETE statement, especially if the table has numerous rows, triggers, indexes, and other dependencies.

The DROP Statement

Removing an object from the database accomplished with the DROP statement.

1
2
DROP TABLE books;
DROP TABLE persons;

When you drop a table it removes all the rows, invalidates dependent objects, removes indexes, constraints and privileges that anyone had on that table. Just as with the CREATE and ALTER statements, there are other DROP statement types.

That concludes our brief introduction to the SQL Data Description Language.