SQL Queries

You've learned all about how to create sql queries to read, write, update and delete data… but you haven't yet learned how to create the tables where you'll be doing the reading, writing, updating and deleting.

So that's what today's podcast is all about, be sure to click the play button above this to listen to the show and then follow along with the notes below.

How to Create a MySQL Database

First thing is first, you need to make sure you've got some sort of application that you can use to manage your databases and your queries.  I like to use TOAD for MySQL but it's obviously up to you which application you want to use.  I even created a simple step by step video tutorial on how to install a database and set up the TOAD program.  I'd highly recommend watching this video even if you don't plan on using TOAD.

Once you've got your database installed and you've got your database management application up and running, it's time to create your first database table.  Before we create a table, we need to create a database!  Thankfully this is simple, all you need to do is write a one line script and run it within your database management application (i.e. TOAD):

create database test123;

So all that's going on here is that we're creating a new database, and it will be named test123, piece of cake!  Once we run that code, we'll be ready to start creating tables inside of our new database.

How to Make a Database Table using MySQL

Next up is creating tables inside our new database. As I mentioned above, make sure to play the podcast episode which is embedded into this post at the very top, you'll learn a lot about the code that I'm about to write.

We're going to create two tables Users and Address, these tables have a one-to-many relationship. First let's create the Users table:

create table users
(
  user_id int(11) auto_increment primary key ,
  username varchar(20),
  password varchar(20)
);

Pretty straight forward, you'll notice that the user_id column has a few extra things added to it in the creation script. This is because it is the primary key of the Users table. When we use auto_increment it grants us the ability of not having to explicitly set a user_id every time we try to insert a row into that table… trust me, it's awesome.

Okay, so now let's create the Address table:

create table address
(
  address_id int(11) auto_increment primary key,
  user_id int(11),
  street_address_1 varchar(255),
  street_address_2 varchar(255),
  region varchar(50),
  zip_code varchar(7),
  country varchar(50),
  foreign key (user_id) references users (user_id)
);

This one is a bit more involved, but it still has the same concepts as the Users table with one exception. The Address table declares a Foreign Key constraint.

This Foreign Key constraint will tell our database to automatically enforce our one-to-many relationship. What will happen now is that when we try to insert a row into the Address table, your new database will automatically check to make sure that the user_id you've specified does indeed exist inside the Users table.

Testing Foreign Key Constraint

There's a simple way to test that your new foreign key is set up correctly. Once you've run the table creation scripts above and your tables were successfully created inside your test database, you can try to insert data into the tables.

Try and run the following insert statement… you should get an error.

insert into address (
   user_id
  ,street_address_1
  ,street_address_2
  ,region
  ,zip_code
  ,country
) VALUES (
   1   -- user_id - IN int(11)
  ,'123 Fake St'  -- street_address_1 - IN varchar(255)
  ,'Unit 283'  -- street_address_2 - IN varchar(255)
  ,'Beverly Hills'  -- region - IN varchar(50)
  ,'90210'  -- zip_code - IN varchar(7)
  ,'USA'  -- country - IN varchar(50)
);

If all goes well you'll see this error:

Lookup Error - MySQL Database Error: Cannot add or update a child row: a foreign key constraint fails (`test123`.`address`, CONSTRAINT `address_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))

Or something similar to that message. This is your foreign key hard at work. It won't let you insert a row into the Address table without first having inserted a row into the Users table with the corresponding user_id of 1 (which is what we specified in the insert statement above).

So now to get things working properly, you should populate some data in the Users table first:

insert into users (
   username
  ,password
) VALUES (
   'username1'  -- username - IN varchar(20)
  ,'password1234!'  -- password - IN varchar(20)
);

Now that you've inserted a user into the Users table, now you can insert your address row:

insert into address (
   user_id
  ,street_address_1
  ,street_address_2
  ,region
  ,zip_code
  ,country
) VALUES (
   1   -- user_id - IN int(11)
  ,'123 Fake St'  -- street_address_1 - IN varchar(255)
  ,'Unit 283'  -- street_address_2 - IN varchar(255)
  ,'Beverly Hills'  -- region - IN varchar(50)
  ,'90210'  -- zip_code - IN varchar(7)
  ,'USA'  -- country - IN varchar(50)
);

Done and done, you've now successfully created two tables, enforced the one-to-many relationship and populated data into both tables!

Congrats you smart little cookie you :)