SQL for Data Definition

Everything in a database begins with structure: before a single row of data exists, someone must declare what tables the database contains and what each column of each table looks like. SQL's tools for this job form its Data Definition Language (DDL).

DDL is the set of SQL commands for three structural operations:

• defining relation schemas (declaring new tables and their design),
• modifying relation schemas (changing an existing table's design), and
• deleting relations (removing tables altogether).

Through DDL, the complete set of relations in a database is specified — and the specification covers more than just table names. It includes each relation's schema, the data type of each attribute, the constraints on those attributes, and even the security and access related authorisations, that is, who is permitted to do what with the data.

Data definition starts with the create statement. This single statement is used both to create a database and to create the tables (relations) inside it — it is, quite literally, the first SQL a database ever sees.

Creation, however, should never be the first step of your thinking. Before creating a database, three design decisions must already be clear:

• the number of tables the database will contain,
• the columns (attributes) that belong in each table, and
• the data type of each of those columns. …