DDL commands have to do with working with the database structure. The main commands are CREATE DATABASE, CREATE TABLE, ALTER TABLE, DROP DATABASE and DROP TABLE.

CREATE DATABASE

CREATE DATABASE creates a new database. It has this basic syntax:

There are several options that can be specified in a WITH clause.

DROP DATABASE

DROP DATABASE permanently removes a database, including all data stored in it. It has this basic syntax:

This command will fail if any user is connected to the database. It also cannot be reversed.

CREATE TABLE

CREATE TABLE creates a new table in the current database. It uses this basic syntax to define the table:

Where:

1. table_name is the name of a table.
2. column_name is the name of a column.
3. data_type is a valid data type.
4. column_constraint is a constraint applying to a single column.
5. table_constraint is a constraint applying to the entire table.

Constraints

Constraints restrict in some way what data can be entered into the database. Attempts to enter data that does not meet the constraints result in an error.

There are two types of constraints: column constraints and table constraints. Two constraints, NOT NULL and DEFAULT, can only be a column constraint. All of the other constraints we’ll cover here have both a column and a table form.

When defining constraints, it is best to define a constraint that involves only a single column as a column constraint. It is also best (and, with the exception of the CHECK constraint, necessary) to define a constraint that involves multiple columns as a table constraint.

Column Constraints

Column constraints have this basic syntax:

Where:

1. constraint_name is an optional name for the constraint. (If no CONSTRAINT clause is provided, a default name will be assigned to the constraint.)
2. expression is an expression that evaluates to a boolean value.
3. default_expr is an expression that evaluates to a value with the same type as the column.
4. reftable is a valid table name in the database.
5. refcolumn is a valid column in reftable. If omitted, it is reftable‘s primary key.
6. action may be (among other less common values): NO ACTION, CASCADE or SET NULL.

Table Constraints

Table constraints have this basic syntax:

Where:

1. constraint_name is an optional name for the constraint. (If no CONSTRAINT clause is provided, a default name will be assigned to the constraint.)
2. expression is an expression that evaluates to a boolean value.
3. column_name is the name of a column.
4. reftable is a valid table name in the database.
5. refcolumn is a valid column in reftable. If omitted, it is reftable‘s primary key.
6. action may be (among other less common values): NO ACTION, CASCADE or SET NULL.

Comparing Column and Table Constraints

When defining constraints, it’s best to define a constraint that only involves a single column as a column constraint, on the column that it involves. It’s best (and, with the exception of the CHECK constraint, necessary) to define a constraint that involves multiple columns as a table constraint.

Suppose a table has a single column defined as the primary key. In this case the column would have a PRIMARY KEY constraint, and should (but does not have to) use column constraint syntax to define the key. Suppose, instead, a table defines its primary key as two columns that when taken together uniquely identify a row (it has a composite primary key, in other words). In this case, it is necessary to use the table constraint syntax for PRIMARY KEY to define the key.

For another example, a CHECK constraint might require a column to have a value between 1 and 50. Although this can be defined as a table constraint, it should be defined as a column constraint on that column, since only one column is involved. Another CHECK constraint might require an item_cost column to be less than a sell_price column. Since two columns are part of the constraint (CHECK (item_cost < sell_price)), it is best to create the constraint as a table constraint — a column constraint on one column can reference another column, so a column constraint is possible, but a table constraint with two columns is easier to understand.