A-level Computing/AQA/Paper 2/Fundamentals of databases/Data definition language





Data Definition Language (DDL) is a way to adjust the structure of a database. You might have created databases in the past using a GUI such as Access or even MySQL. DDL allows you to create databases from pure code including the ability to:
 * Create tables: CREATE TABLE
 * Change the structure of a table: ALTER
 * Delete tables: DROP

To create a table the user needs to define the name of the table and each of the attributes including the data type and length.

Examples of supported data types (depending on the database):

CREATE
You need to know what they all do (as listed above), though you only need to know how to use the CREATE TABLE command. Let's look at how we could have made the crooks table above:

ALTER
An ALTER statement in SQL changes the properties of a table in a relational database without the need to access the table manually.

DROP
Dropping a table is like dropping a nuclear bomb. It is irreversible and generally not recommendedǃ. By running this line of code, the table "crooks" will be removed from the database with no chance of it being recovered unless backups have been previously made.

Setting Primary Keys
Primary keys can be set after table creation via the alter statement.

Primary keys can also be set during table creation

Setting Composite Keys
To set a primary key made up of two columns during table creation you could do something such as this Where the constraint name would be UserId and the table's primary key would be made up of the user_id and the username columns.

This could also be done after table creation: