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:
1CREATE TABLE books
2( book_id VARCHAR(100),
3 book_name VARCHAR(100),
4 author_id NUMBER,
5 editor_id NUMBER)
6;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.
1CREATE TABLE persons
2( person_id NUMBER NOT NULL PRIMARY KEY,
3 name VARCHAR(100),
4 birth_date DATE,
5 gender VARCHAR(30),
6 last_update DATE NOT NULL,
7 updated_by NUMBER NOT NULL
8 );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.
1CREATE FUNCTION get_bookcount(author IN NUMBER)
2 RETURN NUMBER
3 IS bookcount NUMBER(10);
4 BEGIN
5 SELECT count(1)
6 INTO bookcount
7 FROM books
8 WHERE author_id = author;
9 RETURN(bookcount);
10 END;
11/
12
13CREATE INDEX editor_indx ON books(editor_id);
14
15CREATE ROLE book_reader;
16
17CREATE TRIGGER persons_update
18 BEFORE INSERT OR UPDATE ON persons
19 FOR EACH ROW
20 BEGIN
21 :new.last_update := sysdate;
22 END;
23/| Other CREATE Statement Types |
|---|
| CREATE SCHEMA statement |
| CREATE SEQUENCE statement |
| CREATE SYNONYM statement |
| CREATE TRIGGER statement |
| CREATE TYPE statement |
| CREATE VIEW statement |
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.
1ALTER TABLE books MODIFY (book_name NOT NULL);
2ALTER 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.
1ALTER TABLE books ADD CONSTRAINT fk_author
2 FOREIGN KEY (author_id) REFERENCES persons (person_id);
3
4ALTER TABLE books ADD CONSTRAINT fk_editor
5 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.
1ALTER 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.
1ALTER ROLE book_reader IDENTIFIED BY r2Xe135DEw;
2
3ALTER INDEX editor_indx DISABLE;
4
5ALTER TRIGGER persons_update RENAME TO persons_trig;The TRUNCATE Statement
1TRUNCATE TABLE books;The TRUNCATE statement removes all the data from a table. This is very similar to DML statement.
1DELETE 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.
1DROP TABLE books;
2DROP 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.
| Other DROP Statement Types |
|---|
| DROP FUNCTION statement |
| DROP INDEX statement |
| DROP PROCEDURE statement |
| DROP ROLE statement |
| DROP SCHEMA statement |
| DROP SEQUENCE statement |
| DROP SYNONYM statement |
| DROP TRIGGER statement |
| DROP TYPE statement |
| DROP VIEW statement |
That concludes our brief introduction to the SQL Data Description Language.