Learn SQL The Hard Way A Learn Code The Hard Way Book

[next] [prev] [prev-tail] [tail] [up]

Chapter 5
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:

Source 7: ex4.sql
1  INSERT INTO person_pet (person_id, pet_id) VALUES (0, 0);
2  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.

5.1 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:

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

5.2 Extra Credit

  1. Add the relationships for you and your pets.
  2. 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?
  3. 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?

[next] [prev] [prev-tail] [front] [up]

Take An Online Video Course

I will be offering this book as a video course soon. Stay tuned.