105800 Views
82496 Views
47243 Views
47066 Views
45458 Views
38527 Views
Pi-Apps
Intermediate MicroPython
Arduino Alvik
Raspberry Pi Time machine
Now Ad-Free
Guiding Light
Intermediate level MicroPython
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
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 >