Hey everybody, so in this topic I'm going to explain what the foreign key constraint is. A foreign key, think of it as a primary key from one table that can be found within a different table. Using a foreign key, we can establish a link between two tables.
There's two primary benefits to this. In my transactions table, if I were to take a look at the customer ID of who initiated this transaction, I could refer to the customers table, then find the first and last name of that customer. We can do that when we reach the topic of joins, but in this video we're going to focus more or less on just creating foreign keys. Another benefit when you create a foreign key constraint, that would create a link between two tables, which prevents any actions that would destroy that link between them. So here's an example.
I have three tables, employees, products, and transactions. I'm going to create a new table of customers. I'm going to create this table and speed it up. Feel free to pause the video if you need to. Here's my table.
We have a customer ID, which is the primary key, a first name, and a last name. Let's populate our customers table. We have three customers, Fred Fish, Larry Lobster, Bubble Bass. Each has a unique customer ID.
We're going to create a link between our customers table and our transactions table via our customer ID. I'm going to drop our table transactions. Drop table transactions.
We will recreate this table but apply that foreign key constraint. So again I'll speed up the footage. Now what I'm going to do is create a third column to hold our customer ID.
And the data type is int. I would like to add a foreign key constraint to our customer ID column. On the next line, I will type foreign key, list the column we're applying this foreign key constraint to within parentheses, customer ID.
References. Our second table, the Customers table. Customers, then the column of the primary key, which was Customer ID. And that's all you need to do. Then I will select all from my table, Transactions.
Here is our table. We have a transaction ID. This is the primary key, the amount of the transaction, then the customer ID of who initiated that transaction.
Our customer ID column is the foreign key. It points to the customer's table. Based on what the customer ID is, we can find the first and last name of that customer.
To find any active foreign keys, go to your table, in this case transactions, underneath foreign keys, This section will display any foreign keys that are applied to this table. We currently have one. Transactions underscore IBFK underscore one. If you need to drop a foreign key, you would type alter table, the name of the table that has the foreign key constraint, that would be transactions, drop foreign key, then the name of this foreign key.
transactions underscore IBFK underscore one. And that foreign key should be gone. You could also give your foreign key a unique name, alter table, the name of the table, add constraint, then some unique name.
Let's rename that constraint as FK underscore. Customer ID. Foreign key. The name of the column.
Customer ID. References. The name of the second table, customers, then the column that has the primary key, customer ID. So to apply a foreign key to a table that already exists, this would be the syntax.
You don't necessarily need to name the constraint, but if you would like to, just add that line. Add constraint, then some unique name. Let's add this foreign key constraint to a table that already exists.
It worked. If I were to refresh my Navigator window, we now have a foreign key, and it's the one that we gave a name to. Foreign key customer ID. What we're going to do is drop the rows from our transactions table, then reinsert them, but we will add a value for each foreign key. So let's get rid of this.
Delete from transactions. Then select all from transactions. Here we are.
Okay, so let's insert some new rows. I do want to set auto increment to be 1,000, so I'm going to do that before we insert some rows. Alter table transactions.
auto increment equals 1000. So let's take a look at transactions. Then we will insert some new rows. Insert into transactions our values. We had four transactions.
We will need an amount and the customer ID of who initiated the transaction. The first order is for $4.99. Customer number 3 initiated this transaction.
The second order is $2.89. Customer number 2 initiated that transaction. $3.38.
This is also customer 3. They returned back the same day. $4.99. Customer number one initiated that transaction. We have our primary key from the transactions table as well as our foreign key. Each of these customer IDs references the customer ID column from the customers table.
We won't get to explore that until we reach the topic on joins. So the other benefit of a foreign key constraint is that we now have a link between our transactions table and the customers table. MySQL will prevent any actions that would destroy that link between them.
Unless we explicitly drop that foreign key constraint, I'm going to delete some of our customers. DELETE FROM customers WHERE customerId equals 3. We were right, it doesn't appear that we can do so. Cannot delete or update a parent row?
A foreign key constraint fails. So yeah, that's the foreign key constraint. It's a primary key from one table that's found also within a different table. But when we're working with that different table, we would refer to that key as a foreign key.
We'll have more practice with this when we reach the topic on joins. But yeah, that is the foreign key constraint in MySQL.