Foreign Keys

We've learned a lot about unique keys and primary keys. 0:00 Unique keys guaranteed data is unique within a given column. 0:03 Primary keys also guaranteed the uniqueness within a column but 0:07 they go a step further. 0:10 They guarantee that each row in a table can be identified and 0:11 those links from one table to another. 0:16 In this video we'll focus on another really important type of key, 0:19 the foreign key. 0:23 You can think of a foreign key as a pointer or 0:25 reference from one table to another. 0:27 With this example the ProductID column in the cell table 0:29 is appointed to the ProductID in the Products table. 0:33 The one in the ProductID column in the sales table points to the corresponding 0:37 one row in the product table. 0:42 In this case scarf, purple, wool. 0:45 The two points to the row in the product table that says gloves, white. 0:48 Finally the three points to the row in the product table that says, coat, 0:52 women's, long. 0:57 The ProductID column in the cell table is the foreign key. 0:58 There are two facets to creating a foreign key, 1:03 while we're not creating a foreign key ourselves it's good to know, first, 1:06 the database designer has to match the columns properly in both tables. 1:11 For example, we just saw that the sale and 1:18 product relationship with the ProductID being referenced in this sales table. 1:21 Database systems aren't smart enough to know that these two columns are related 1:26 without an additional instruction when the tables are created. 1:31 This instruction is called Constraint specifically, a Foreign Key constraint. 1:35 [SOUND] You can think of a constraint as a rule that the database has to enforce, 1:39 once the database is instructed to enforce a foreign key constraint 1:45 between two tables, it will not allow the value to be added to a table containing 1:50 a foreign key unless the value also exists in a table with the primary key. 1:56 Being able to ensure the validity of our data relationships in this manner is 2:02 called referential integrity and 2:07 is one of the most important aspects of a good relational database. 2:09 Now, that we know what a foreign key is let's see it in action. 2:14 Here we have a table called EyeColor, 2:18 It has an integer primary key that we'll reference under the table. 2:20 Lets add two rows to the EyeColor table now, brown and blue. 2:25 Imagine that we have a person table and we're gonna insert three people, 2:32 the first person will give brown eyes. 2:36 Designated with the one to much the value in the primary key column 2:39 in the EyeColor table. 2:43 The second person will give blue eyes with the color ID value of two, 2:45 the third person will give green eyes. 2:49 Let's try and set that record with the value of three, it shouldn't let us but 2:53 it does. 2:57 The person symbol has an EyeColorID value that refers to a record 2:58 in the EyeColor table that doesn't exist with the ID of three. 3:03 This would indicate that the constraints had not been created 3:07 on the EyeColorID relationship between the two tables. 3:11 Again, without the constraint, the database doesn't care if we're trying to 3:15 refer to a value that doesn't exist in the primary key of the EyeColor table. 3:19 [SOUND] But if the database has proper constraints in place, 3:24 it will not allow us to insert the row for Jessica with the eye color of three 3:28 until it exists in the primary key in the EyeColor table. 3:33 Now that we've added the green color to the EyeColor table, 3:37 let's try and insert Jessica again with her green eyes and it works. 3:42 In review, we've learned about three different types of keys, unique, 3:47 primary and foreign keys. 3:52 Unique keys guarantee that no data repeats within a particular column. 3:55 Primary Keys are like unique keys but they go further, 4:00 they guarantee that each row on the table can be uniquely identified. 4:03 Primary keys also work together with the last kind of key, foreign keys. 4:07 To give us the ability to relate two tables of data together 4:11 on common data values. 4:15 Last, we learned that foreign key constraints are the way that database 4:17 is enforce these relationships. 4:21 Without foreign key constraints, our data lacks referential integrity. 4:23 There are other types of database keys as well, check the teacher's notes for 4:29 more information on all types of database keys. 4:33