Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
In this video we'll create the TICKETS table and see how to add foreign key constraints!
Related Links
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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 upYou need to sign up for Treehouse in order to set up Workspace
Sign up