101591 Views
79842 Views
45596 Views
44332 Views
40991 Views
33419 Views
Raspberry Pi Time machine
Now Ad-Free
Guiding Light
Sync Files on your Pis, with Syncthing
NextCloud
Buddy Jr.
Introduction to FreeCAD for Beginners
Building a Robot Arm with Raspberry Pi and PCA9685
Building User Authentication for Static Sites with FastAPI
Mastering Pydantic for Robust Data Validation
Mastering Markdown for Documentation with Jekyll
Introduction to Rust
KevsRobots Learning Platform
20% Percent Complete
By Kevin McAleer, 3 Minutes
Module 3 focuses on creating tables in SQLite databases and understanding the principles of schema design. We will explore how to define table structure, data types, constraints, and primary keys.
Tables are the foundation of a relational database. They organize and store data in a structured manner. In this section, we will explore the components of table structure.
A table consists of rows, also known as records or tuples, and columns, also known as fields or attributes.
To create tables in a SQLite database, we use the SQL CREATE TABLE statement. This statement specifies the table name, column names, data types, and constraints.
CREATE TABLE
# Create a table connection.execute(''' CREATE TABLE books ( id INTEGER PRIMARY KEY, title TEXT, author TEXT, year INTEGER ) ''')
In this example, we create a table named “books” with four columns: “id,” “title,” “author,” and “year.” The INTEGER data type is used for the “id” and “year” columns, while TEXT is used for the “title” and “author” columns. The “id” column is defined as the primary key.
INTEGER
TEXT
A primary key uniquely identifies each record in a table. It ensures data integrity and serves as a reference point for establishing relationships between tables. In SQLite, primary keys can be defined using the PRIMARY KEY constraint.
PRIMARY KEY
# Create a table with a primary key connection.execute(''' CREATE TABLE students ( id INTEGER PRIMARY KEY, name TEXT, age INTEGER, grade TEXT ) ''')
In this example, we create a “students” table with an “id” column as the primary key. Each record in the table will have a unique “id” value.
Sometimes, we need to modify the structure of existing tables. SQLite provides the ALTER TABLE statement to add, modify, or delete columns from a table.
ALTER TABLE
# Add a new column to a table connection.execute("ALTER TABLE students ADD COLUMN email TEXT")
In this example, we add a new “email” column to the “students” table. This new column will store email addresses for each student.
When designing a database schema, it’s important to consider several factors to ensure efficient data storage and retrieval. Some key considerations include:
NOT NULL
UNIQUE
By understanding these considerations, we can design efficient and well-structured databases.
< Previous Next >