English subtitles

← Foreign Keys - Intro to Relational Databases

Get Embed Code
4 Languages

Showing Revision 3 created 05/24/2016 by Udacity Robot.

  1. In database terminology, a column
    with a references constraint on it,
  2. is also called a foreign key.
  3. Now this has always sounded like
    a bit of a weird term to me,
  4. like it was the key to a lock
    from far across the sea, but
  5. a foreign key is just a column or
    a set of columns in one table,
  6. that uniquely identifies
    a row in another table.
  7. It's possible for
  8. a table to have two or more foreign
    keys, actually that's really common.
  9. For instance, consider a database of
    students grades in various courses.
  10. Here we've got a table of students
    names and their ID numbers.
  11. And the ID is a primary key
    because names aren't unique.
  12. And we have a table of courses, with
    course names and course IDs as well.
  13. And then we have the grades table,
  14. which has foreign keys
    into both of those tables.
  15. Here are the same tables,
    but this time drawn out.
  16. Usually a foreign key will point to
    the primary key of the referenced table.
  17. That's because the whole point of
    a foreign key is to uniquely identify
  18. a row in the referenced table, and
    the table's primary key does that.
  19. Now here's a quiz.
  20. These tables are for
  21. a more complex forum app than
    the one you worked on last lesson.
  22. This app allows users to log in and
    post comments, and
  23. also vote on other people's posts.
  24. Take a look at these tables, and see if
    you can figure out which columns should
  25. be primary keys, and which columns
    should be foreign keys referencing them.
  26. In the users table,
    we have a username or handle or
  27. alias, and the user's full name.
  28. In this posts table,
  29. we have the post content, the author's
    username, and an ID number for the post.
  30. And in the votes table,
    we have the id number of a post,
  31. the username of somebody who
    voted on that post, and a 1 or
  32. minus 1 for whether they liked it or
    disliked it.
  33. So for each one of these columns, if it
    should be a primary key of that table,
  34. put a P in that box.
  35. If it should be a foreign key, put an F.
  36. If it shouldn't be either one,
    leave the box empty.
  37. Also, although there's no check boxes
    for them, think about if any of
  38. these tables should have primary
    keys with more than one column.