Creating the Tickets Table5:12 with Ben Deitch
In this video we'll create the TICKETS table and see how to add foreign key constraints!
We've got our concerts table, and we've got our ticket holders table. 0:00 All that's left is tying them together with the tickets table. 0:03 Let's start by reusing a lot of the code we have here. 0:07 First, let's change all instances of ticket holders to say tickets. 0:10 And then, let's starting looking at the columns. 0:32 The ID column actually looks okay. 0:36 So let's keep moving with the concert ID column. 0:39 Let's change first name to concert ID, and instead of using a varchar, 0:42 let's make this a small int to match with the ID from the concerts table. 0:51 Also since this column should always have a match in the ID 0:56 column from the concerts table, we'll want to add a constraint to make sure we don't 1:00 insert any concerts that don't exist. 1:05 This kind of constraint is called a foreign key constraint. 1:09 When you have a column that only allows values from another table's primary key, 1:13 you refer to that column as a foreign key. 1:18 To add a foreign key restraint in SQL light, 1:22 you just add references after the data type. 1:26 Then specify the table, add the column and parenthesis. 1:29 So right after small int, let's type references, concerts, 1:33 and then in parentheses, ID, perfect. 1:39 Next up is the ticket holder ID column, which is another foreign key. 1:43 Let's change the last name to ticket holder ID. 1:48 Given an integer data type to match what's in the ticket holders table. 1:53 And finally, add the foreign key constraint by adding references, 1:59 ticket holders, and then in parentheses, ID. 2:04 To finish up the table, let's get rid of the email column, and 2:10 delete that last comma. 2:14 Then let's state working on some insert statements to help make sure 2:17 everything is set up correctly. 2:20 Since we're using auto increment, we're fine using null for the IDs. 2:22 But let's delete the rest of this data before we move on. 2:28 For the first two insert statements, let's insert values that match up with rows, and 2:39 the concerts, and ticket holders table. 2:44 Let's go with one and three for the first one, and eight and two for the second. 2:48 So if we wanted to check say this first one, we 2:56 would make sure that there was a concert with ID of 1, 2:59 and a ticket holder with the ID of 3. 3:04 For the last two inserts, let's test that our foreign key constraints 3:09 are working like they're supposed to. 3:13 Let's change the third INSERT to have a valid CONCERT but 3:16 not a valid TICKET_HOLDER. 3:19 Maybe something like 5 and 5000. 3:22 And for the fourth INSET let's do the opposite. 3:24 An invalid CONCERT but a real TICKET_HOLDER. 3:29 Let's go with -10 and 4. 3:33 Now, lets talk about what we expect to happen when we run this. 3:37 We'll start by dropping the table if it exist. 3:41 Then we'll create the table, and insert the first and second rows just 3:46 fine before failing on the third row because of the foreign key constraint. 3:51 Let's run it and see what happens. 3:58 Thanks, it inserted the bad data. 4:02 It turns out that in SQLite, foreign keys are a feature that you need to turn on. 4:06 So at the top of the query, let's add a new line and 4:11 type PRAGMA FOREIGN_ 4:15 kEYS =ON, and end with a semicolon. 4:21 Then, let's run this again, and perfect, we failed the foreign key constraint. 4:27 And if we look over at the TICKETS table, it only has the two valid rows in it. 4:32 Back in our query. 4:37 Now that foreign keys is turned on, we can get rid of this line. 4:40 Now, lets change the invalid data and see if we can't get everything to work. 4:46 Let's change 5,000 to 1, and -10 to 4. 4:50 We run it again, and there we go. 4:59 We've finally done creating our tables. 5:03 In our next video, we'll investigate what you should do, 5:05 if you end up needing to make some changes. 5:08
You need to sign up for Treehouse in order to download course files.Sign up