When you have new entities and relationships to store in your database, you can create a new database
table using the
CREATE TABLE statement.
CREATE TABLE IF NOT EXISTS mytable ( column DataType TableConstraint DEFAULT default_value, another_column DataType TableConstraint DEFAULT default_value, … );
The structure of the new table is defined by its table schema, which defines a series of columns. Each column has a name, the type of data allowed in that column, an optional table constraint on values being inserted, and an optional default value.
If there already exists a table with the same name, the SQL implementation will usually throw an error,
so to suppress the error and skip creating a table if one exists, you can use the
IF NOT EXISTS clause.
Different databases support different data types, but the common types support numeric, string, and other miscellaneous things like dates, booleans, or even binary data. Here are some examples that you might use in real code.
||The integer datatypes can store whole integer values like the count of a number or an age. In some implementations, the boolean value is just represented as an integer value of just 0 or 1.|
||The floating point datatypes can store more precise numerical data like measurements or fractional values. Different types can be used depending on the floating point precision required for that value.|
The text based datatypes can store strings and text in all sorts of locales. The distinction between the various types generally amount to underlaying efficiency of the database when working with these columns.
Both the CHARACTER and VARCHAR (variable character) types are specified with the max number of characters that they can store (longer values may be truncated), so can be more efficient to store and query with big tables.
||SQL can also store date and time stamps to keep track of time series and event data. They can be tricky to work with especially when manipulating data across timezones.|
||Finally, SQL can store binary data in blobs right in the database. These values are often opaque to the database, so you usually have to store them with the right metadata to requery them.|
|Docs: MySQL, Postgres, SQLite, Microsoft SQL Server|
We aren't going to dive too deep into table constraints in this lesson, but each column can have additional table constraints on it which limit what values can be inserted into that column. This is not a comprehensive list, but will show a few common constraints that you might find useful.
||This means that the values in this column are unique, and each value can be used to identify a single row in this table.|
||For integer values, this means that the value is automatically filled in and incremented with each row insertion. Not supported in all databases.|
||This means that the values in this column have to be unique, so you can't insert another row with the same value in this column as another row in the table. Differs from the `PRIMARY KEY` in that it doesn't have to be a key for a row in the table.|
||This means that the inserted value can not be `NULL`.|
||This allows you to run a more complex expression to test whether the values inserted are valid. For example, you can check that values are positive, or greater than a specific size, or start with a certain prefix, etc.|
||This is a consistency check which ensures that each value in this column corresponds to
another value in a column in another table.
For example, if there are two tables, one listing all Employees by ID, and another listing their payroll information, the `FOREIGN KEY` can ensure that every row in the payroll table corresponds to a valid employee in the master Employee list.
Here's an example schema for the Movies table that we've been using in the lessons up to now.
CREATE TABLE movies ( id INTEGER PRIMARY KEY, title TEXT, director TEXT, year INTEGER, length_minutes INTEGER );
In this exercise, you'll need to create a new table for us to insert some new rows into.
Databasewith the following columns:
Name A string (text) describing the name of the database
Version A number (floating point) of the latest version of this database
Download_count An integer count of the number of times this database was downloaded