Sample Video Frame

Created by Zed A. Shaw Updated 2024-02-17 04:54:36

Exercise 4: Insert Referential Data

In the last exercise you filled in some tables with people and pets. The only thing that's missing is who owns what pets, and that data goes into the person_pet table like this:

INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
INSERT INTO person_pet VALUES (0, 1);

Again I'm using the explicit format first, then the implicit format. How this works is I'm using the id values from the person row I want (in this case, 0) and the id from the pet rows I want (again, 0 for the Unicorn and 1 for the Dead Robot). I then insert one row into person_pet relation table for each "connection" between a person and a pet.

When you create a table like this you are creating a "relation". Technically every table is a "relation" but I find that confusing because only some tables are actually used to relate (connect) tables to other tables. The person_pet table allows you to relate the data in person to the data in pet. Later you'll learn to link these tables with special queries called "joins" that connect one table, to another, using equality. In this book I will only cover the "baby" version of joins, relations, and foreign keys because that topic is more advanced and also becomes less standardized between databases.

What You Should See

I'll just piggyback on the last exercise and run this right on the ex3.db database to set these values:

$ sqlite3 -echo ex3.db < ex4.sql 
INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
INSERT INTO person_pet VALUES (0, 1);

Study Drills

Previous Lesson Next Lesson

Register for Learn SQL the Hard Way

Register today for the course and get the all currently available videos and lessons, plus all future modules for no extra charge.