**Course Title:** SQLite Mastery: Lightweight Database Management **Section Title:** Creating and Managing SQLite Databases **Topic:** Best practices for defining primary keys and foreign keys in SQLite. As a database developer, defining primary keys and foreign keys is crucial to ensure data integrity and maintain relationships between tables. In this topic, we will discuss the best practices for defining primary keys and foreign keys in SQLite, and provide practical examples to help you apply these concepts effectively. ### Primary Keys in SQLite A primary key is a unique identifier for each record in a table. It ensures that each record can be uniquely identified and helps maintain data integrity. In SQLite, you can define a primary key when creating a table using the PRIMARY KEY constraint. **Example: Defining a primary key when creating a table** ```sql CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ); ``` In this example, the `customer_id` column is defined as the primary key using the `INTEGER PRIMARY KEY` constraint. This means that each record in the `customers` table will have a unique `customer_id` value. ### Best practices for defining primary keys in SQLite: * Use a unique and meaningful identifier, such as an ID or a code, as the primary key. * Use the `INTEGER PRIMARY KEY` constraint to define the primary key, which will automatically create an auto-incrementing ID. * Avoid using a composite primary key, which consists of multiple columns. * Use the `NOT NULL` constraint to ensure that the primary key cannot be null. ### Foreign Keys in SQLite A foreign key is a field in a table that references the primary key of another table. It helps establish relationships between tables and ensures data integrity. In SQLite, you can define a foreign key when creating a table using the FOREIGN KEY constraint. **Example: Defining a foreign key when creating a table** ```sql CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); ``` In this example, the `customer_id` column in the `orders` table is defined as a foreign key that references the `customer_id` column in the `customers` table. ### Enabling Foreign Key Constraints in SQLite By default, foreign key constraints are disabled in SQLite. To enable them, you need to run the following command: ```sql PRAGMA foreign_keys = ON; ``` **Note:** This setting only applies to the current database connection and needs to be enabled every time the database is opened. ### Best practices for defining foreign keys in SQLite: * Use a foreign key to establish relationships between tables. * Use the `FOREIGN KEY` constraint to define the foreign key, specifying the table and column that it references. * Use the `REFERENCES` keyword to specify the table and column that the foreign key references. * Use the `ON DELETE` and `ON UPDATE` clauses to specify the action to take when the parent record is deleted or updated. ### Real-world example Suppose we have two tables: `customers` and `orders`. We want to establish a relationship between these tables, where each order belongs to a customer. ```sql -- Create the customers table CREATE TABLE customers ( customer_id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT UNIQUE ); -- Create the orders table with a foreign key to customers CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, customer_id INTEGER, order_date DATE, FOREIGN KEY (customer_id) REFERENCES customers (customer_id) ); -- Enable foreign key constraints PRAGMA foreign_keys = ON; -- Insert some data into customers and orders INSERT INTO customers (customer_id, name, email) VALUES (1, 'John Doe', 'john@example.com'); INSERT INTO customers (customer_id, name, email) VALUES (2, 'Jane Doe', 'jane@example.com'); INSERT INTO orders (order_id, customer_id, order_date) VALUES (1, 1, '2022-01-01'); INSERT INTO orders (order_id, customer_id, order_date) VALUES (2, 1, '2022-01-15'); INSERT INTO orders (order_id, customer_id, order_date) VALUES (3, 2, '2022-02-01'); ``` In this example, we define a `customers` table with a primary key `customer_id` and an `orders` table with a primary key `order_id` and a foreign key `customer_id` that references the `customer_id` column in `customers`. We then enable foreign key constraints and insert some data into both tables. This establishes a relationship between `customers` and `orders`, ensuring that each order is associated with a valid customer. **Additional Resources:** * [SQLite Documentation: database constraints and indexes](https://www.sqlite.org/lang_createtable.html) * [SQLite Documentation: CREATE TABLE](https://www.sqlite.org/lang_createtable.html) * [W3Schools: SQL FOREIGN KEY Constraint](https://www.w3schools.com/sql/sql_foreignkey.asp) **What's Next?** In the next topic, 'Using SELECT statements for querying data,' we will cover the basics of querying data in SQLite, including filtering and sorting results. **Do you have any questions or need further clarification on this topic? Leave a comment below.