Creating a Relational Database (SQL)

Although covered in the videos the code for creating a SQL database is shown below:

Tthe SQL field types you are expected to use a quick reminder is shown below.

• TEXT (or VARCHAR)- used to store text

• INT - stores integer values (use this for a boolean field - 0 means false and 1 means true)

• 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

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

fieldName dataType PRIMARY KEY

When creating a database you can add suitable validation to ensure your data is correct.

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

fieldName dataType NOT NULL

An example would be:

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

The SQL code below would implement a range check.

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).