Solution: Update database creation script - MySQL Tutorial

- [Instructor] Okay, so we're going to go over the solution for creating the database creation script. So one of the things I want to point out first is that when we look at the lucid chart for the actual database, we want to identify the order in which to create the tables. So first we look for tables that have foreign keys. So we can see here that foreign keys are on table order and order item. Now the reason why we want to identify those is because those have to be created in a specific order. So any table that has foreign keys to other tables needs those other tables to be created first before their table can be created. Because if you're creating a foreign key and you don't have that table created, how will SQL know what that foreign key is a foreign key to? So first here we have for order customer ID and salesperson ID, which are foreign keys to customer table and salesperson table. So those two have to be created before order table can be created. And then we have product, which is a foreign key of order item. And order table in turn is also a foreign key to the order item table. So in this case, this database order item table has to be created last. And product can be created anytime before order item and you can even create it before you create the order table, just as long as it's created before the order item table and customer and salesperson both have to be created before the order table can be created. So in any instance, you have to do customer, salesperson, order, product, order item, although product could be before order table also. And then another thing I wanted to bring up is the drop table if exists. So if the table already exists and you run the script, it won't create the table, it'll error out, or it might just complain and tell you that it already exists so it skipped it. So we add in certain things like you could do drop table if exists customer, so that if the table actually exists, it drops it and then you can create the table, no problem. Another way to do it is if you only create the table if it does not exist. So depending on whether you know already that it exists and you want it to not be there 'cause you want to recreate it, or if you just want it to be created if it doesn't exist, you pick which one you want to use. But the thing that we're going to add here is we're going to update the foreign key. So we have orders and we know that there's going to be a foreign key to customer ID. Customer ID is here and one to salesperson ID. And salesperson is here. So we're just going to add in the constraints. So it's a constraint for... So we have the foreign and that references, customer table, customer ID. Then I'm going to add a constraint. Now, the reason why we are adding this constraint is that we don't want any orders to be created that don't have a customer and don't have a salesperson. So if the salesperson ID or the customer ID or both don't exist in their respective tables, this cannot be created. That row in the database cannot be created. Okay, so we have those two, and then we're going to add the one for order item, which we know is going to be... And now we'll add the product. And there we have it. And then we will run the test to make sure that it works. In this case, because it's not actually returning anything, it's just creating the database that you won't see anything on the output unless there's an error. So if there's nothing in the output, that means it was correct. So let's run the code. And since we have nothing here and no errors, it looks like it was all good.