Bummer! This is just a preview. You need to be signed in with a Basic account to view the entire video.

Preview

Start a free Basic trial
to watch this video

Sign up for Treehouse

Creating the Ticket Holders Table

5:43 with Ben Deitch

In this video we'll create the TICKET_HOLDERS table and learn about the AUTOINCREMENT keyword.

Insert Statements

INSERT INTO TICKET_HOLDERS VALUES (1, 'Jessica', 'Rodgers', 'jessrodg23@hotmail.com'); INSERT INTO TICKET_HOLDERS VALUES (2, 'Mary', 'O’Hara', 'mohara@gmail.com'); INSERT INTO TICKET_HOLDERS VALUES (3, 'Dominik', 'Smith', 'dsmith1120@aol.com'); INSERT INTO TICKET_HOLDERS VALUES (4, 'Marcella', 'Childs', 'mcchilds@gmail.com');

  • 0:00

    One table down, two to go.

  • 0:02

    Now that we're done with our CONCERTS table,

  • 0:05

    let's start by clicking reset to give us a nice clean canvas.

  • 0:09

    And delete that comment again,

  • 0:11

    then let's move onto creating our ticket holders table.

  • 0:15

    If you'd like to take a stab at it yourself, pause me and

  • 0:18

    see what you come up with.

  • 0:20

    It should be mostly the same as what we did with the CONCERTS table.

  • 0:24

    Okay, here's what I came up with, first we're going

  • 0:29

    to create the table and name it Ticket_Holders.

  • 0:37

    Then we're going to add the ID column, give it an INT data type,

  • 0:42

    and then make it the PRIMARY KEY.

  • 0:45

    Since we'll probably have more than 32,000 TICKET_HOLDERS,

  • 0:49

    we can't use small INT for the data type.

  • 0:52

    Instead, we should use INT,

  • 0:54

    which will let us have just over two billion TICKET_HOLDERS.

  • 0:58

    Next, let's declare the FIRST_NAME column as

  • 1:03

    a VARCHAR with space for up to 255 characters.

  • 1:08

    Then let's copy and paste that line twice.

  • 1:17

    Rename FIRST_NAME to LAST_NAME and FIRST_NAME to EMAIL,

  • 1:23

    get rid of this last comma, and there we go.

  • 1:28

    Let's run it.

  • 1:31

    And now we've got a TICKET_HOLDERS table, great work.

  • 1:34

    Back in the query, let's copy and

  • 1:36

    paste on the insert statements from the teacher's notes to give us some test data.

  • 1:41

    Then let's run it and write, if we want to recreate the table on each run,

  • 1:47

    we need to start by dropping the table.

  • 1:50

    Let's add a line at the top and

  • 1:53

    type, DROP TABLE TICKET_HOLDERS and

  • 1:58

    a semi colon, and then try running it again.

  • 2:04

    Perfect, also, if we'd like to check what's in the table,

  • 2:08

    we can always use a select statement.

  • 2:10

    Below our insert statements, let's select everything from

  • 2:16

    the TICKET_HOLDERS table, SELECT star FROM TICKET_HOLDERS.

  • 2:21

    Then if we run it again,

  • 2:23

    we should be able to see what's in the table without needing to click on it.

  • 2:28

    Perfect, we're almost done with this table,

  • 2:31

    there's just one more thing we should talk about, and that's the ID column.

  • 2:36

    Each time we insert a new row, we have to come up with a unique ID,

  • 2:40

    which can be kind of a hassle.

  • 2:42

    Luckily, SQL gives us an easy way to deal with this by using

  • 2:47

    the AUTOINCREMENT keyword.

  • 2:49

    If you had AUTOINCREMENT after an integer PRIMARY KEY, then anytime you try

  • 2:54

    to insert null it will just give you the next number, let's try it out.

  • 2:59

    Let's have a space after PRIMARY KEY, and then type AUTOINCREMENT.

  • 3:06

    And then instead of using ID's one, two,

  • 3:12

    three and four, let's go with one, two, null and null.

  • 3:19

    And if we run it, we get an error that

  • 3:22

    AUTOINCREMENT is only allowed on an INTEGER PRIMARY KEY.

  • 3:27

    You know how I've mentioned that sequel it doesn't use data types?

  • 3:31

    Well, there is one exception and it's the INTEGER PRIMARY KEY

  • 3:37

    if you declare an INTEGER PRIMARY KEY sequel will actually

  • 3:42

    make sure that whatever goes in there is an INTEGER.

  • 3:47

    So it makes sense that AUTOINCREMENT,

  • 3:50

    which requires integers would only be allowed on an INTEGER PRIMARU KEY.

  • 3:56

    Okay, let's change INT to INTEGER,

  • 4:00

    and try running it again.

  • 4:06

    But now it's telling us that there's no TICKET_HOLDERS tables.

  • 4:09

    Since last time we got an error in our CREATE TABLE statement,

  • 4:14

    the table was dropped but it never got re-created.

  • 4:19

    So now we're trying to drop a table that doesn't exist, lucky for

  • 4:24

    us there's an easy solution that helps make testing a lot easier.

  • 4:29

    And the DROP TABLE statement right after the word table,

  • 4:34

    we just need to add IF EXISTS to make it only drop the table if it actually exists.

  • 4:40

    Let's run it, and now we can finally see our AUTOINCREMENT in action.

  • 4:46

    We didn't specify IDs for Dominik or Marcella but thanks to AUTOINCREMENT,

  • 4:52

    the database was able to pick up where we left off and give us three and four.

  • 4:57

    Pretty cool, right?

  • 4:59

    Now that we know how AUTOINCREMENT works, let's go ahead and use null for

  • 5:03

    all the IDs, instead of just the last two.

  • 5:10

    Also while we're changing things,

  • 5:13

    just like we added IF EXISTS to the DROP TABLE statement.

  • 5:16

    We can do something similar with the CREATE TABLE statement,

  • 5:20

    by adding IF NOT EXIST before the table name.

  • 5:27

    This way, we won't try to create any already existing tables.

  • 5:32

    All right, let's run this one more time to make sure everything works.

  • 5:37

    And in the next video,

  • 5:39

    we'll tie everything together with the tickets table.

You need to sign up for Treehouse in order to download course files.

Sign up