**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Working with Multiple Tables: Joins and Relationships **Topic:** Understanding table relationships and foreign keys In this topic, we will explore the concept of table relationships and foreign keys in SQLite. Understanding these concepts is crucial when working with multiple tables in a database. By the end of this topic, you will be able to define and implement relationships between tables in your SQLite databases. ### Table Relationships Table relationships describe how data in one table is related to data in another table. There are three primary types of relationships: 1. **One-to-One (1:1) Relationship**: In a one-to-one relationship, a single record in one table is related to a single record in another table. This type of relationship is rare, but it can be useful when you need to store additional information that is not essential to the primary table. *Example*: Suppose we have two tables, `users` and `user_profiles`. Each user has a unique profile, and each profile belongs to a specific user. ```sql CREATE TABLE users ( id INTEGER PRIMARY KEY, username TEXT NOT NULL ); CREATE TABLE user_profiles ( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, profile_picture TEXT, FOREIGN KEY (user_id) REFERENCES users (id) ); ``` 2. **One-to-Many (1:N) Relationship**: In a one-to-many relationship, a single record in one table is related to multiple records in another table. *Example*: Suppose we have two tables, `orders` and `order_items`. Each order can have multiple order items, and each order item belongs to a specific order. ```sql CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL, order_date DATE NOT NULL ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_name TEXT NOT NULL, quantity INTEGER NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (id) ); ``` 3. **Many-to-Many (M:N) Relationship**: In a many-to-many relationship, multiple records in one table are related to multiple records in another table. This type of relationship requires a third table, often called a bridge table or junction table. *Example*: Suppose we have three tables, `students`, `courses`, and `enrollments`. Each student can enroll in multiple courses, and each course can have multiple students. ```sql CREATE TABLE students ( id INTEGER PRIMARY KEY, student_name TEXT NOT NULL ); CREATE TABLE courses ( id INTEGER PRIMARY KEY, course_name TEXT NOT NULL ); CREATE TABLE enrollments ( student_id INTEGER NOT NULL, course_id INTEGER NOT NULL, PRIMARY KEY (student_id, course_id), FOREIGN KEY (student_id) REFERENCES students (id), FOREIGN KEY (course_id) REFERENCES courses (id) ); ``` ### Foreign Keys Foreign keys are a crucial concept in database relationships. A foreign key is a column in a table that references the primary key of another table. This establishes a relationship between the two tables. Here are some key points about foreign keys: * **Referential Integrity**: SQLite enforces referential integrity by default. This means that you cannot insert a record in a table that references a non-existent record in the related table. * **ON DELETE**: SQLite provides three actions that you can take when deleting a record in a table that has a related record in another table: * `ON DELETE CASCADE`: Deletes the related record as well. * `ON DELETE SET NULL`: Sets the foreign key value to NULL. * `ON DELETE SET DEFAULT`: Sets the foreign key value to its default value. * **ON UPDATE**: SQLite provides three actions that you can take when updating a record in a table that has a related record in another table: * `ON UPDATE CASCADE`: Updates the related record as well. * `ON UPDATE SET NULL`: Sets the foreign key value to NULL. * `ON UPDATE SET DEFAULT`: Sets the foreign key value to its default value. ### Example Use Case Suppose we are building a simple e-commerce database with tables for `orders`, `order_items`, `products`, and `customers`. We want to ensure that each order is related to a specific customer, and each order item is related to a specific order and product. ```sql CREATE TABLE customers ( id INTEGER PRIMARY KEY, customer_name TEXT NOT NULL ); CREATE TABLE orders ( id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date DATE NOT NULL, FOREIGN KEY (customer_id) REFERENCES customers (id) ); CREATE TABLE products ( id INTEGER PRIMARY KEY, product_name TEXT NOT NULL ); CREATE TABLE order_items ( id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, FOREIGN KEY (order_id) REFERENCES orders (id), FOREIGN KEY (product_id) REFERENCES products (id) ); ``` ### Conclusion In this topic, we covered the concept of table relationships and foreign keys in SQLite. We discussed the types of relationships (one-to-one, one-to-many, and many-to-many) and explored how to establish relationships between tables using foreign keys. **Key Takeaways**: * Table relationships describe how data in one table is related to data in another table. * There are three primary types of relationships: one-to-one, one-to-many, and many-to-many. * Foreign keys are used to establish relationships between tables. * SQLite enforces referential integrity by default. * `ON DELETE` and `ON UPDATE` actions can be used to specify the behavior when deleting or updating records in related tables. **Additional Resources**: * SQLite Documentation: [SQL As Understood By SQLite: FOREIGN KEY](https://sqlite.org/lang_foreignkey.html) * Tutorials Point: [SQLite Foreign Key](https://www.tutorialspoint.com/sqlite/sqlite_foreign_key.htm) **Leave a comment below if you have any questions or need help with implementing table relationships and foreign keys in your SQLite database.**