# Structured Query Language/Data Definition Language

← Data Manipulation Language | Data Control 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 office. The records in the office 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:
office :
| id_office | INTEGER |
| name | VARCHAR(20) |
| description | VARCHAR(255) |
| place_number | INTEGER |
| available | SMALLINT |
| next_inspection | DATE |
Now the table office can be used and filled as the tables reunion, employee, project and members:
office :
| id_office | name | description | place_number | available | next_inspection |
| 1 | Show room | | 100 | 1 | 2011-03-24 |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 |
| 5 | Reunion room | | 20 | 1 | 2010-05-12 |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 |
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. office). 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 id_office has been defined as a primary key, the column description can be null and the column available has 1 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 has_video_projector to indicate if we can project a slideshow:
* The table before the statement:
office :
| id_office | name | description | place_number | available | next_inspection |
| 1 | Show room | | 100 | 1 | 2011-03-24 |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 |
| 5 | Reunion room | | 20 | 1 | 2010-05-12 |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 |
* Query:
* The table after the statement:
office :
| id_office | name | description | place_number | available | next_inspection | has_video_projector |
| 1 | Show room | | 100 | 1 | 2011-03-24 | 0 |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 | 0 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 | 0 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 | 0 |
| 5 | Reunion room | | 20 | 1 | 2010-05-12 | 0 |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 | 0 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 | 0 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 | 0 |
The column has_video_projector has been added at the end. The column has been filled with the default value.

### DROP COLUMN clause

Now let's remove the column next_inspection:
* The table before the statement:
office :
| id_office | name | description | place_number | available | next_inspection | has_video_projector |
| 1 | Show room | | 100 | 1 | 2011-03-24 | 0 |
| 2 | Big room | The biggest room. | 200 | 1 | 2010-06-03 | 0 |
| 3 | Open space | The developer open space. | 50 | 1 | 2011-03-15 | 0 |
| 4 | Hall | The entrance. | 20 | 1 | 2010-10-28 | 0 |
| 5 | Reunion room | | 20 | 1 | 2010-05-12 | 0 |
| 6 | Actual office | This office is under construction. | 5 | 0 | 2010-06-03 | 0 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 2011-03-15 | 0 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 2011-02-11 | 0 |
* Query:
* The table after the statement:
office :
| id_office | name | description | place_number | available | has_video_projector |
| 1 | Show room | | 100 | 1 | 0 |
| 2 | Big room | The biggest room. | 200 | 1 | 0 |
| 3 | Open space | The developer open space. | 50 | 1 | 0 |
| 4 | Hall | The entrance. | 20 | 1 | 0 |
| 5 | Reunion room | | 20 | 1 | 0 |
| 6 | Actual office | This office is under construction. | 5 | 0 | 0 |
| 7 | Temporary office | The office used while the actual is under construction. | 5 | 1 | 0 |
| 8 | Coffee machine | The room where you can pause. | 5 | 1 | 0 |
The column next_inspection 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.