Create Table Products

What we’ll learn

In the previous chapters, we started implementing our basic customer relationship management system. We already implemented the customers_db database and the customers table. In this chapter, we’re going to implement the products table.

Below are some features of the products table:

• It will have a primary key id. This will be an integer, not null, and auto-incremented.
• It will have a name, which is going to be a string, not null, and unique. It will have a price, which is going to be a decimal number and not null. Let’s check how many tables are currently in our database by executing the query given below:

Command to create the products table

Let’s create the products table. We already know the command to create a table. Let’s see its incarnation for the products table:

We get a Succeeded response, which means the query was executed successfully. The only new thing here is the numeric (15, 2), which is the type for the price column. It denotes that this column is going to hold real numbers, or decimal numbers, as we sometimes call them. These numbers will have two decimal digits, and their maximum number of digits will be 15. Now, if we enter show tables, we see the new table in the list of tables for the current database.

Let’s also see the structure of this new table:

mysql> show create table products;
+----------+---------------------+
| Table    | Create Table        |
+----------+---------------------+
| products | CREATE TABLE `products` (
           `id` int(11) NOT NULL AUTO_INCREMENT,
           `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
           `price` decimal(15,2) NOT NULL,
            PRIMARY KEY (`id`)
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci        
+----------+---------------------+
1 row in set (0.01 sec)

We can see that this new table has exactly three columns with the specifications that we gave while entering the create table command. We can verify the results by using the same command in the code box above.

Unique name#

Now, we need to make sure that no products have the same name. In other words, the name should be unique. We also need to make sure that we can quickly search for products using the name as criteria. In order to do that, we need to create a unique index on the name column. We already know how to create a unique index. Let’s try that by executing the command given in the box below:

Let’s verify the existence of the new unique index:

mysql> show create table products;
+----------+---------------------+
| Table    | Create Table        |
+----------+---------------------+
| products | CREATE TABLE `products` (
           `id` int(11) NOT NULL AUTO_INCREMENT,
           `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
           `price` decimal(15,2) NOT NULL,
            PRIMARY KEY (`id`),
            UNIQUE KEY `products_name_uidx` (`name`)
           ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 
+----------+---------------------+
1 row in set (0.00 sec)

As we can see, there’s a new row in the specification.

UNIQUE KEY `products_name_uidx` (`name`)

It specifies the unique index on the name column.