SQL Data Definition Language (DDL)

SQL DDL provides clauses for database creation and modification as well as the creation and modification of table structures. Table creation usually involves definition of the following:

1. The domain associated with each column.
2. Integrity constraints.
3. Indices for each table.
4. Security and authorization information.
5. Physical storage structure information.

We will address the first two points only.

Domain Types

The SQL92 standard allows for the specification of a variety of domains. These are based on underlying system data types:

• char(n): fixed-length character string, with user-specified length.
• varchar(n): variable-length character string, with user-specified maximum length.
• int: integer (length is machine-dependent).
• smallint: small integer (length is machine-dependent).
• numeric(p, d): fixed-point number with user-specified precision, consists of p digits (plus a sign) and d of p digits to the right of the decimal point. For example, numeric(3, 1) allows 21.5 to be stored exactly but not 210.5.
• real or double precision: floating-point or double-precision floating-point numbers, with machine-dependent precision.
• float(n): floating-point, with user-specified precision of at least n digits.
• date: a calendar date, containing four digit year, month, and day of the month.
• time: the time of the day in hours, minutes, and seconds.

Note: SQL Server provides additional types. See the TransactSQL Help facility for details.

Integrity Constraints

These are constraints that apply to the columns of a table. For example, if a column (or columns) is defined to be a primary key then this indicates to the DBMS that the values of the column must be unique and that they cannot be null. The DBMS may then enforce this integrity constraint for any tuple added to the table.

SQL92 provides support for user defined integrity constraints as well as primary key and foreign key (see notes on Model Transformation) integrity constraints (we will not consider user defined constraints).

A primary key constraint may be defined thus:

      primary key (column_name, ...)

A table may have at most one primary key constraint. As mentioned above, this ensures uniqueness, and ensures that the column will not contain null values.

A foreign key constraint may be defined thus:

      foreign key (column_name, ...)
           REFERENCES table_name

A table may have several foreign key constraints. The table name in the references clause identifies the parent table. This constraint indicates that the coulumn_name specified in the foreign key clause corresponds to a primary key in the table definition of the parent table.

Table Creation

SQL provides the CREATE TABLE clause for table creation. The general form is:

        CREATE TABLE table_name
              (column_name  domain_type [NOT NULL] [default] ...
               integrity_constraint ...)

Note that [] indicates an optional keyword. Note also, that default may be NULL or a literal. The following example is for the ASSISTANT table discussed previously:

        CREATE TABLE ASSISTANT
              (SID         char(11),
               ClassName   char(5) NOT NULL,
               Hours       integer,
               primary key (SID),
               foreign key (ClassName) REFERENCES CLASS)

SID is the primary key, of length 11 characters. ClassName is a foreign key that references the primary key of the table CLASS, is of length 5 characters, and cannot be NULL.

Table Modification

SQL also provides the DROP clause to remove tables from a database and the ALTER clause to change the structure of existing tables (i.e. add or remove columns).

Readings: None (see the TransactSQL Help facility for SQL Server specific syntax).