Table Creation

Containers to store your app data

Creating a Table

Tables can be created using SQLite Studio.

Open a database

Use SQLite Studio to connect your
Message Board database.

Click the “Create Table” button

Click the “Create Table” button which is
a blue and white square with a green plus on it.

Fill in a table name

Give your table a name, lowercase and plural.
This one will be the users table.

Click the “Add Column” button

The “Add column” button is a blue and
white square with a green stripe.

Fill in the column information

Specify the relevant column name, data type and size.

Select Primary or Foreign Key if required

You may select Primary Key or Foreign key if needed.

Click the OK button

Add the new column to the set of planned
changes for the database.

Repeat the process for all required columns

Fill in the relevant information for each
column needed in the users table.

Click the “Apply changes” button

The changes are not automatically saved,
you need to apply them.

Creating a table using the shell

We can also create tables using a predefined script
stored in a file, which is helpful for backups.

Create a new file schema.sql in your db folder

A database schema describes the
structure of a database using code.

Create a new transaction

BEGIN TRANSACTION;

COMMIT;

Drop existing table

If the table already exists, we need to delete it first.

BEGIN TRANSACTION;

DROP TABLE IF EXISTS users;

COMMIT;

Write a create table statement

BEGIN TRANSACTION;

DROP TABLE IF EXISTS users;
CREATE TABLE users;

COMMIT;

Add a row describing the primary key

BEGIN TRANSACTION;

DROP TABLE IF EXISTS users;
CREATE TABLE users(
  user_id INTEGER PRIMARY KEY AUTOINCREMENT
);

COMMIT;

Add rows describing the attributes

CREATE TABLE users(
  user_id INTEGER PRIMARY KEY AUTOINCREMENT,
  username TEXT(30),
  password TEXT(64),
  first_name TEXT(20),
  last_name TEXT(20),
  email TEXT(50),
  photo BLOB(10000)
);

Create another table for messages

CREATE TABLE users(
  ...
);

DROP TABLE IF EXISTS messages;
CREATE TABLE messages(
  message_id INTEGER PRIMARY KEY AUTOINCREMENT,
  content TEXT(300),
  time_created TEXT(30),
  user_id INTEGER FOREIGN_KEY
);

Remember to include any required foreign keys

Verify structure of schema

There should be a “begin transaction” and a “commit”
with two “drop table” and two “create table” commands between.

Save the file

Very important, don’t forget to save!

Open the database from shell

sqlite3 message-board.db

Read and execute your SQL file using SQLite

.read schema.sql

Check the database visually

Open the database in SQLite Studio and refresh,
you should be able to see your new table structure.

Thumbs Up!

Table Creation: Complete!

Take me to the next chapter!

Loading...