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:

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:

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:

id_office	name	description	place_number	available	next_inspection
office
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:

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

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

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

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
office
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
office
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:

id_office	name	description	place_number	available	next_inspection	has_video_projector
office
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
office
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.

DROP TABLE <table name>;

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.

TRUNCATE TABLE <table_name>;