Preview
Video Player
00:00
00:00
Creating the Tickets Table
5:12In 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