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 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.
The “Add column” button is a blue and
white square with a green stripe.
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.
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.
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.
