CREATE Statements
CREATE DATABASE Statement
The CREATE DATABASE statement will create an empty database.
If a database with the same name does exist a warning similar to below would be produced as opposed to an error.
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:
Create a new table in a specified database
Define the fields
Set any primary/foreign keys
Add any constraints/validation
Specify the Database Engine
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.
VARCHAR - used to store text
INT - stores integer values
FLOAT - used to store real numbers
DATE - stores a date in the format YYYY-MM-DD
TIME - stores a time in the format hh:mm:ss
There is also a DATETIME type although not in the course which is in the format YYYY-MM-DD HH:MM:SS
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 :
Primary Key
Foreign Key
Not Null
Auto Increment
Check
Constraints: Primary Key
This specifies that a primary key of one field (or more) has to be unique:
If it is a primary key consisting of one field (an atomic key) then you can put PRIMARY KEY after the field definition in your CREATE statement:
fieldName dataType PRIMARY KEY
You can also place the PRIMARY KEY statement at the end of the CREATE clause as shown below:
If it is a primary key consisting of more than one field (a compound/composite key) then you can put PRIMARY KEY at the end of your CREATE statement and enter the fields inside the brackets as shown below.
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:
A view that only shows the valid data ( which can be referenced like a table but would be populated with ).
Triggers that can be executed before or after data is deleted or modified (outwith the scope of the course but may be required in Projects)
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.