Foreign keys

As you know, relationships exist between the tables in a relational database. This means that the rows in a table are usually associated to the rows in another table or multiple tables.

Creating relationships between tables

Let's see an example. Suppose that our online store sells several types of products. We don't want to display them all together, because it would be confusing for most users. Instead, we need to separate our products by category.

So, first we will create a table for the product categories. We need to store the category name, a description, and of course an id that will be the primary key:

CREATE TABLE product_category
(
  id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  description TEXT NOT NULL,
  UNIQUE INDEX unq_name (name)
)
  DEFAULT CHARACTER SET utf8
  ENGINE = InnoDB;

Of course, this table will not be useful unless we also have a way to associate each product to one category. To do this, we will create a new column...