Data Definition Language

A data definition language (DDL) is a computer language used to create and modify the structure of database objects in a database. These database objects include views, schemas, tables, indexes, etc. This term is also known as data description language in some contexts, as it describes the fields and records in a database table.

Data Definition Language (DDL) #

The CREATE TABLE, ALTER TABLE and DROP TABLE statements, which are collectively known as Data Definition Statements. (In contrast, the UPDATE, INSERT, and DELETE statements are often described as Data Manipulation Statements).

• Create Table

• Alter Table

• Drop Table

We now know much of the SQL vocabulary and have examined the SQL query in some detail, beginning with its basic syntax. So far, we have been ignoring the process of creating databases and tables. We have assumed that these database objects existed currently on our system. Today we finally create these objects.

The syntax of the CREATE statements can range from the extremely simple to the complex, depending on the options our database management system (DBMS) supports and how detailed we want our database when designing.

Note: The examples used today were generated using Personal Oracle7. Please see the documentation for our specific SQL implementation for any minor differences in syntax.

CREATE TABLE Statement #

The process of creating a table is far more standardized than the CREATE DATABASE statement. Here’s the basic syntax for the CREATE TABLE statement:

Syntax:

CREATE TABLE table_name
(     field1 datatype [ NOT NULL ],
field2 datatype [ NOT NULL ],
field3 datatype [ NOT NULL ]...)

A simple example of a CREATE TABLE statement follows.

SQL>  CREATE TABLE BILLS (
NAME CHAR(30),
AMOUNT NUMBER,
ACCOUNT_ID NUMBER);
Table created.

Analysis:

• This statement creates a table named BILLS. Within the BILLS table are three fields: NAME, AMOUNT, and ACCOUNT_ID.

• The NAME field has a data type of character and can store strings up to 30 characters long.

• The AMOUNT and ACCOUNT_ID fields can contain number values only.

Table Name #

• When creating a table, several constraints apply when naming the table.

• First, the table name can be no more than 30 characters long.

• Because Oracle is case insensitive, we can use either uppercase or lowercase for the individual characters.

• However, the first character of the name must be a letter between A and Z.

• The remaining characters can be letters or the symbols _, #, $, and @.

• Of course, the table name must be unique within its schema.

• The name also cannot be one of the Oracle or SQL reserved words (such as SELECT).

Field Name #

• The same constraints that apply to the table name also apply to the field name.

• However, a field name can be duplicated within the database.

• The restriction is that the field name must be unique within its table.

• For instance, assume that we have two tables in our database: TABLE1and TABLE2.

• Both of these tables could have fields called ID.

• We cannot, however, have two fields within TABLE1 called ID, even if they are of different data types.

Field’s Data Type #

• If we have ever programmed in any language, we are familiar with the concept of data types, or the type of data that is to be stored in a specific field.

• For instance, a character data type constitutes a field that stores only character string data.

• The LONG RAW data type is often called a binary large object (BLOB) in other database management systems.

• It is typically used to store graphics, sound, or video data.

• Although relational database management systems were not originally designed to serve this type of data, many multimedia systems today store their data in LONG RAW, or BLOB, fields.

• The ROWID field type is used to give each record within our table a unique, no duplicating value.

• Many other database systems support this concept with a COUNTER field (Microsoft Access) or an IDENTITY field (SQL Server).

NULL Value #

• SQL also enables us to identify what can be stored within a column.

• A NULL value is almost an oxymoron, because having a field with a value of NULL means that the field actually has no value stored in it.

• When building a table, most database systems enable us to denote a column with the NOT NULL keywords.

• NOT NULL means the column cannot contain any NULL values for any records in the table.

• Conversely, NOT NULL means that every record must have an actual value in this column.

The following example illustrates the use of the NOT NULL keywords.

SQL> CREATE TABLE BILLS (NAME CHAR(30) NOT NULL,AMOUNT NUMBER,ACCOUNT_ID NOT
NULL);

Table Storage and Sizing #

Most major RDBMS have default settings for table sizes and table locations. If we do not specify table size and location, then the table will take the defaults. The defaults may be very undesirable, especially for large tables. The default sizes and locations will vary among the implementations. Here is an example of a CREATE TABLE statement with a storage clause (from Oracle).

SQL> CREATE TABLE TABLENAME (COLUMN1 CHAR NOT NULL, COLUMN2 NUMBER, COLUMN3
DATE) TABLESPACE TABLESPACE NAME STORAGE INITIAL SIZE, NEXT SIZE,
MINEXTENTS value, MAXEXTENTS value, PCTINCREASE value);
Table created.

Creating a Table from an Existing Table #

• The most common way to create a table is with the CREATE TABLE command.

• However, some database management systems provide an alternative method of creating tables, using the format and data of an existing table.

• This method is useful when we want to select the data out of a table for temporary modification.

• It can also be useful when we have to create a table similar to the existing table and fill it with similar data.

The syntax for Oracle follows.

Syntax:

SQL> CREATE TABLE NEW_TABLE(FIELD1, FIELD2, FIELD3) AS (SELECT FIELD1, FIELD2,
FIELD3  FROM OLD_TABLE <WHERE...>

This syntax allows us to create a new table with the same data types as those of the fields that are selected from the old table. It also allows us to rename the fields in the new table by giving them new names.

SQL> CREATE TABLE NEW_BILLS(NAME, AMOUNT, ACCOUNT_ID) AS (SELECT * FROM BILLS
WHERE AMOUNT < 50);
Table created.

ALTER TABLE Statement #

Many times our database design does not account for everything it should. Also, requirements for applications and databases are always subject to change. The ALTER TABLE statement enables the database administrator or designer to change the structure of a table after it has been created.

The ALTER TABLE command enables us to do two things:

• Add a column to an existing table

• Modify a column that already exists

The syntax for the ALTER TABLE statement is as follows:

Syntax:

SQL> ALTER TABLE table_name
<ADD column_name data_type; | MODIFY column_name data_type;>

The following command changes the NAME field of the BILLS table to hold 40 characters:

SQL> ALTER TABLE BILLS MODIFY NAME CHAR(40);
Table altered.

DROP TABLE Statement #

SQL provides a command to completely remove a table from a database. The DROP TABLE command deletes a table along with all its associated views and indexes. After this command has been issued, there is no turning back. The most common use of the DROP TABLE statement is when we have created a table for temporary use. When we have completed all operations on the table that we planned to do, issue the DROP TABLE statement with the following syntax:

Syntax:

SQL> DROP TABLE table_name;

Here’s how to drop the NEW_BILLS table:

SQL> DROP TABLE NEW_BILLS;
Table dropped.