What is DDL? / Data Definition Language / Role of Data Definition Language / What can a DDL statement do? / Important aspects of DDL statements
Data Definition Language (DDL)
As specified in the name, DDL is used to
define database schemas. With DDL statements we can do the following;
- To create databases.
For example we can create a database named
University in MySQL as follows;
CREATE DATABASE University;
- To create table structures.
For example, we can create a table named
Student in Oracle as follows;
CREATE TABLE student (Regno NUMBER(10),
Name VARCHAR(30));
- To change the structure of the tables.
We can alter the structure of student
table by adding/dropping columns, by changing the size of the accepted values
etc. For example, the following statement adds a new column with Student table
in Oracle;
ALTER TABLE student ADD COLUMN Phone
Number(10);
- To remove tables.
We can delete the whole table structure
Student using the following statement in Oracle;
DROP TABLE student;
- To rename tables.
To rename an existing table, we can use
the following query in Oracle;
RENAME student TO student_table;
- To define referential integrities.
We can define referential integrities
like primary key constraints, check constraints, specific type etc using DDL statements.
For example, the following query creates a table Teacher with primary key constraint.
CREATE TABLE Teacher(Id NUMBER(3)
PRIMARY KEY, Name VARCHAR(30));
- To analyze information.
We can analyze a table, index or
clusters for handling performance related issues.
- To add comments to the Data Dictionary.
We can add comments to tables, table
columns when they are stored in Data Dictionary. For example, the statement will
add a comment ‘Name of the teacher’ with the column definition Name of Teacher
table.
COMMENT ON COLUMN Teacher.Name IS ‘Name
of the teacher’;
When we execute DDL statements, it does
the following things;
1. It does the required things as specified through query. [For example, as mentioned above]
2. Update the special table called Data Dictionary. The Data Dictionary gets updated every time you execute one of the DDL statements.