More SQL Please: CREATE TABLE

A database has a collection of tables which contain the data to be stored. The CREATE TABLE statement is used to create a new table in a database.

A table needs to be created before any data can be inserted into or queried from it. When a table is created it will persist until it is deleted using a DROP TABLE statement. This example shows how to create a table posts that can be used to store message board posts from users.

A CREATE TABLE must contain the table name, any columns you wish to create, and the associated data type for that column.

On this website, every table shown at the top of the page is created using a CREATE TABLE statement that is run automatically in the background. This section will show examples of creating new tables in the same database using more CREATE TABLE statements.

Database engines have various ways to show the structure of an existing table. SQLite has a series of special "system" tables you can SELECT to get information about the database/tables themselves. pragma_table_info(...) is one such table that we query to see information about the posts table we just created. Other database engines use special syntax such as DESCRIBE ... for the same purpose.

Most tables will contain a column named something like id. The name doesn't have any special meaning other than to indicate that this is the column that will be used to uniquely identify a row. Typically an id is either a number that increments as you add new rows, or a uniquely random set of characters such as a UUID. This is also the column that is used to create relationships between tables. In this case our posts table also contains a column called user_id which is how we associate a user with the posts they create. See more about table relationships in JOIN, FOREIGN KEY, and Schema Design.

You may notice the id column declaration in our posts table is followed by the keywords PRIMARY KEY. This is what is known as a Constraint and it tells the database it must enforce uniqueness of this column. It is not required to delcare a primary key but it is considered best practice for various reasons including data integrity and performance. See more about primary keys in PRIMARY KEY.

CREATE TABLE posts(
    id INTEGER PRIMARY KEY, 
    user_id INTEGER,
    content TEXT,
    created_at TEXT );

SELECT name, type 
FROM pragma_table_info('posts');

| name | type | |------------|---------| | id | INTEGER | | user_id | INTEGER | | content | TEXT | | created_at | TEXT |

Once a table is created, it persists in the database. Trying to recreate an existing table results in an error. The modifier IF NOT EXISTS can be applied to only create a table if it doesn't already exist. This can be useful if you have some code or process that automatically creates your tables for you and you want to be able to run it multiple times without causing an error.

This example actually does nothing when run because the table 'users' already exists.

Try removing IF NOT EXISTS from the example and running it again. Notice how it now returns an error because the table 'users' already exists.

CREATE TABLE IF NOT EXISTS users(
    id INTEGER PRIMARY KEY, 
    name_first TEXT, 
    name_last TEXT, 
    age INTEGER, 
    email_address TEXT, 
    created_at TEXT );

| |

Most database engines have the ability to create "generated" columns, which are columns where the value is generated by some operation on the other columns in the table. This example shows an example of combining the first and last names for a user into a generated column.

Generated columns can be created as either STORED or VIRTUAL columns. A virtual column has its value computed when the row is read, for example at the time of a SELECT statement including the column. A stored column has its value computed when the row is written to the database, for example at the time of an INSERT statement.

Note that a slightly different system table pragma_table_xinfo(...) is used here to include generated columns.

CREATE TABLE user_names(
    user_id INTEGER PRIMARY KEY, 
    name_first TEXT, 
    name_last TEXT,
    name_full AS 
        (name_first || ' ' || name_last) VIRTUAL 
    );

SELECT name, type 
FROM pragma_table_xinfo('user_names');

| name | type | |------------|---------| | user_id | INTEGER | | name_first | TEXT | | name_last | TEXT | | name_full | |

You can populate the contents of a created table using the AS keyword followed by a SELECT statement.

This example uses this feature to create a copy of a table with data from a specific date.

This is a simple technique for copying data but be aware that CREATE TABLE ... AS will not copy table constraints, indexes, table permissions and other metadata that a database might associate with a table. See Backup and Recovery for details on recommended techniques for backing up data in relational databases.

CREATE TABLE users_2023 AS
SELECT * FROM users
WHERE created_at < '2024';

SELECT name_first, name_last, created_at 
FROM users_2023;

| name_first | name_last | created_at | |------------|-----------|---------------------| | James | Mitchell | 2023-04-30 09:20:00 | | Simon | Chen | 2023-06-15 14:30:00 | | Zara | Amini | 2023-08-22 11:15:00 | | James | Weber | 2023-11-05 15:45:00 |

A table can created as a "temporary" table using the CREATE TEMPORARY TABLE modifier. A temporary table is a table that only exists until you terminate your client session with the database server. At that point, any temporary tables are deleted and cleaned up automatically.

Different database engines may use different syntax to create temporary tables. They also may handle permissions differently. Some engines make temporary tables accessible only to the creating session. Others like SQLite create them in a separate temporary database file.

Temporary tables have various use cases. Because they are persisted between statements, they can be used to break down complex queries into smaller ones or store the intermediate results of calculations.

This example shows how you could break down a larger table into smaller groups to be able to perform analytical queries on different demographics.

CREATE TEMPORARY TABLE users_gen_x AS
SELECT * FROM users
WHERE age >= 44 AND age < 59;

CREATE TEMPORARY TABLE users_millennials AS
SELECT * FROM users
WHERE age >= 28 AND age < 44;

CREATE TEMPORARY TABLE users_gen_z AS
SELECT * FROM users
WHERE age >= 15 AND age < 28;

SELECT name_first, name_last, age
FROM users_millennials;

| name_first | name_last | age | |------------|-----------|-----| | James | Mitchell | 30 | | Zara | Amini | 30 | | Charlotte | Davies | 30 | | Elena | Moreno | 40 | | Priya | Amini | 31 |