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:

View Source file ex4.sql Only

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:

View Source file ex4.sh-session Only

$ 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

• Add the relationships for you and your pets.

• Using this table, could a pet be owned by more than one person? Is that logically possible? What about the family dog? Wouldn't everyone in the family technically own it?

• Given the above, and given that you have an alternative design that puts the pet_id in the person table, which design is better for this situation?