CREATE Statements

CREATE DATABASE Statement

The CREATE DATABASE statement will create an empty database.

The SQL statement below would create a database called pupils - (only if one didn’t already exist).

CREATE TABLE Statement

The CREATE TABLE statement allows you to:

The syntax is shown below with an example:

Syntax:

CREATE TABLE [IF NOT EXISTS] tbl_name (

fieldName1 fieldType,

fieldName2 fieldType

...

)

Example

The default database engine in MYSQL used to be ISAM  (MySQL 5.5.5) but since then has been INNODB. The INNODB engine offers one feature that is advantageous and that is that it supports FOREIGN KEY constraints (covered here). This means that foreign keys, inserts, updates, and deletes are checked to ensure they do not result in inconsistencies across different tables.

SQL Data Types

Although already covered (here) the SQL field types you are expected to use a quick reminder is shown below.

Constraints

A constraint is a limit that is placed on a piece of data. The constraints we will use at Advanced Higher are specified when a Table is being created :

Constraints: Primary Key

This specifies that a primary key of one field (or more) has to be unique:

fieldName dataType PRIMARY KEY

Constraints: Foreign Key

The Foreign Key constraint ensures that a foreign key is a non null primary key in another table (referential integrity).

When creating a field you can declare the field type and then assign it as shown below.

fieldName dataType FOREIGN KEY REFERENCES tableName (fieldName)

Or at the end of the CREATE statement you can list the foreign key(s).

Either way please do remember that the foreign key has to be created first.

Constraints: Foreign Key ( MySQL example)

Constraints: NOT NULL

This is specified in the field declaration and ensures that a field contains a value and cannot be empty.

mySQL syntax:

fieldName dataType NOT NULL

An example would be: 

Constraints: AUTO INCREMENT

This is specified in the field declaration for a numerical field and will increase the value every time a new row is inserted. And if a row is deleted that had the value 5 and a new one is inserted the new value  would be 6. This ensures entity integrity as there is no chance of there being a duplicate value.

mySQL syntax:

A point to note is that when using an INSERT statement a value would not be specified for an AUTO INCREMENT field.

Constraints: CHECK (restricted choice)

The check constraint below would implement a restricted choice ensuring that the option entered is a valid one contained in the specified values.

mySQL syntax:

Constraints: CHECK (range check)

The check constraint below would implement a range check.

mySQL syntax:

Constraints: CHECK (length check)

The check constraint below would implement a range check.

mySQL syntax:

Constraints: NOT NULL (Presence Check)

This is specified in the field declaration and ensures that a field contains a value and cannot be empty. 

mySQL syntax:

CHECK Constraints issues in MySQL

In the current version of MySQL (5.x) the CHECK constraints are not executed. The new version currently 8.0.16 does support this but at present isn’t installed on the system.

A way to implement the same functionality of check constraints in a project would be:

Create Database Statement Example

The SQL statements below would create the Travel Agency Database and the 4 tables. Please note that the tables that contain no foreign keys are created first.  This is crucial as you cannot reference a foreign key before it has been created.