Page 1 of 2
© Claire Ellul
Database Definition Language
Database definition language (DDL) is used to create and destroy databases and
database objects such as tables and constraints. The commands are primarily
used by database administrators when a database is being built initially, when
a database is being modified for a new application or when a database is being
removed from a system. The commands are the first ones to be used when
creating a database, and must be used before data manipulation language or
select statements are applied to the database.
DDL statements can be run directly against the database using command-line
tools, or through GUI-based database design tools such as Oracle Enterprise
Manager. DDL statements modify the database structure, and are executed
immediately they are typed into the system.
Three basic commands exist in DDL as follows:
CREATE
This command allows the database administrator to add elements to the
database. These could be tables, constraints, primary keys, foreign keys or
sequences. (The CREATE statement can also be used to create a schema to
hold the tables in the database).
The following statement will create a table called rooms into a schema called
ucfscde. The table has attributes
NI_Number
Name
Surname
Department
Grade
Salary
create table ucfscde.employees (
NI_Number character varying(25),
Name character varying(50),
Surname character varying(50),
Department character varying(50),
Grade character varying(15),
Salary integer);
Page 2 of 2
© Claire Ellul
Alter
Once an object such as a table has been created in a database, any
modifications to this object can be made in two ways:
1. Delete the object and recreate it as a new version.
2. Use the Alter statement to modify the object.
The first option (delete and recreate) is obviously fine as long as no-one has
started adding data to the database, but will cause an issue once data has been
entered, as all the data will be lost when the table is deleted.
The following statement adds a field called DATE_OF_BIRTH to the EMPLOYEES
table:
ALTER TABLE ucfscde.EMPLOYEES
ADD (DATE_OF_BIRTH DATE);
Drop
This statement is used to remove unwanted tables, constraints, keys and other
objects from the database. This command should be used with great care, as
most databases do not offer you the option to cancel the process once it has
been started.
DROP TABLE ucfscde.EMPLOYEES;
will delete the EMPLOYEES table from the ucfscde schema in the database.
Note that ALL DATA will also be deleted.
In a similar manner, the DROP COLUMN statement can be used to remove the
DATE_OF_BIRTH column from the EMPLOYEES table – this usage combines the
ALTER and the DROP commands:
ALTER TABLE ucfscde.EMPLOYEES
DROP COLULMN DATE_OF_BIRTH;
Once again, all data in the column will be deleted. There will often be no
warning before this happens.