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
[edit | edit source]The exhaustive syntax of the CREATE statement for the tables is as follows:
CREATE TABLE <table name>
(<column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>][, <column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>]]*
[,[ CONSTRAINT <constraint name>]
{
PRIMARY KEY (<column name>[, <column name>]*)
|
UNIQUE ([VALUE|<column name>[, <column name>]*])
|
FOREIGN KEY (<column name>[, <column name>]*) REFERENCES <table name> (<column name>[, <column name>]*)[ ON DELETE CASCADE][ ON UPDATE CASCADE]
|
CHECK (<predicate>[{ AND| OR} <predicate>]*)
}
]*
);
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:
CREATE TABLE office
(
id_office INTEGER PRIMARY KEY NOT NULL,
name VARCHAR(20) NOT NULL,
description VARCHAR(255),
place_number INTEGER NOT NULL,
available SMALLINT NOT NULL DEFAULT 1,
next_inspection DATE NOT NULL
);
- The table after the statement:
| 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:
| 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
[edit | edit source]The exhaustive syntax of the ALTER statement for the tables is as follows:
ALTER TABLE <table name>
{
ADD[ COLUMN] <column name> <column type>[ NOT NULL][ PRIMARY KEY| DEFAULT <value>]
|
ALTER[ COLUMN] <column name>[ SET DEFAULT <default option>| DROP DEFAULT]
|
DROP[ COLUMN] <column name>
|
ADD[ CONSTRAINT <constraint name>]
{
PRIMARY KEY (<column name>[, <column name>]*)
|
UNIQUE ([VALUE|<column name>[, <column name>]*])
|
FOREIGN KEY (<column name>[, <column name>]*) REFERENCES <table name> (<column name>[, <column name>]*)[ ON DELETE CASCADE][ ON UPDATE CASCADE]
|
CHECK (<predicate>[{ AND| OR} <predicate>]*)
}
|
DROP CONSTRAINT <constraint name>
};
The ALTER statement is used to modify a table. It can be used on a table with records in it.
ADD CONSTRAINT clause
[edit | edit source]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:
ALTER TABLE office ADD CONSTRAINT unique_name_and_description UNIQUE (name, description);
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
[edit | edit source]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:
ALTER TABLE office DROP CONSTRAINT unique_name_and_description;
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
[edit | edit source]Let's add a new column has_video_projector to indicate if we can project a slideshow:
- The table before the statement:
| 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:
ALTER TABLE office ADD has_video_projector SMALLINT DEFAULT 0;
- The table after the statement:
| 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
[edit | edit source]Now let's remove the column next_inspection:
- The table before the statement:
| 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:
ALTER TABLE office DROP COLUMN next_inspection;
- The table after the statement:
| 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
[edit | edit source]The DROP TABLE statement is used to remove a table altogether, its content (data) as well as its definition.
DROP TABLE <table name>;
Read more: DROP (Wikipedia)
TRUNCATE statement
[edit | edit source]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.
TRUNCATE TABLE <table_name>;