Structured Query Language/Data Definition Language

Data Definition Language is used to modify the schema of the database. It will never impact the user rights for the database. Otherwise, it can erase records in some tables. It describes three statements: CREATE, ALTER and DROP.

CREATE statement
The exhaustive syntax of the CREATE statement for the tables is as follows:

The CREATE statement is used to create a new table with no record. Let's create the table. The records in the  table will contain a technical id, the name of the office, a description, the number of available places, the availability and the date for the next office security control:
 * Query:


 * The table after the statement:

Now the table  can be used and filled as the tables ,  ,   and  :

The statement starts with CREATE TABLE, to indicate that what we want to create is a table. It's followed by the name of the table (i.e. ).  The name of the table is followed by parentheses which describe all the columns of the table. The descriptions of the columns are separated by a comma. Each description contains the column name (for instance, id_office), the column type (INTEGER, VARCHAR, CHAR, DATE, etc...), an optional nullability information (nothing to indicate that the column can be null or NOT NULL to indicate that the column can't be null) and the optional keyword DEFAULT followed by a default value or the optional keyword PRIMARY KEY to indicate that the column is a primary key. If no default value is defined, NULL is the default value. If NOT NULL is defined, the column can't have NULL as default value.

You can see that the column  has been defined as a primary key, the column   can be null and the column   has   as default value.

ALTER statement
The exhaustive syntax of the ALTER statement for the tables is as follows:

The ALTER statement is used to modify a table. It can be used on a table with records in it.

ADD CONSTRAINT clause
This clause allows to add a constraint on the table as it could be done at the table creation time. Let's add a unicity constraint on both the name and the description of the office:
 * Query:

Now we can not insert a row with the same name and description of an already existing row and we can not update a row with the same name and description of another row. However, we can insert a row with only the same name or only the same description.

DROP CONSTRAINT clause
This clause allows to remove an existing constraint on the table by its name. Let's remove the preceding unicity constraint on both the name and the description of the office:
 * Query:

Now we can insert a row with the same name and description of an already existing row and we can update a row with the same name and description of another row once again.

ADD COLUMN clause
Let's add a new column  to indicate if we can project a slideshow:


 * The table before the statement:


 * Query:


 * The table after the statement:

The column  has been added at the end. The column has been filled with the default value.

DROP COLUMN clause
Now let's remove the column :


 * The table before the statement:


 * Query:


 * The table after the statement:

The column  has been removed. If you want to remove a column, you need to remove any constraint applied on it (for instance, you could not remove the name or the description column if there is still the unique_name_and_description unicity constraint).

DROP TABLE statement
The DROP TABLE statement is used to remove a table altogether, its content (data) as well as its definition.

Read more: DROP (Wikipedia)

TRUNCATE statement
TRUNCATE quickly removes all data from a table without changing the table's structure, typically bypassing a number of integrity-enforcing and logging mechanisms.

The statement is logically (though not physically) equivalent to the DELETE statement without a WHERE clause. Therefore it's not part of the Data Definition Language (DDL); it's part of the Data Manipulation Language (DML). We describe it here because DROP/DELETE/TRUNCATE are often confused with each other.