Chapter 5 ■ MySQL priMerFinally, notice that while I added a primary key, in this case the AUTO_INCRMENT data type to the
plants table, I did not add one to the readings table. This is so that we can store any number of rows in the
readings table and that it is entirely possible that the data will not be unique. More precisely, the values read
from the sensors may be identical from two or more readings. Thus, I left the uniqueness factor off of the
definition of the readings table.
I should also note a consequence of using the AUTO_INCREMENT data type to uniquely identify rows.
While conceptually there cannot be more than one plant named the same in the same location in the
plants table (even if there were three ferns on the deck, most likely you’d name them differently), the fact
that AUTO_INCREMENT is an artificial uniqueness mechanism means you could very well enter the same
data twice resulting in different AUTO_INCREMENT values. So, a bit of caution is prudent when working with
AUTO_INCREMENT.
There are two handy tools you can use when designing tables. First, you can use the SHOW CREATE TABLE
command to see the actual SQL command to re-create the table. In fact, the SHOW CREATE can be used for
any object such as SHOW CREATE TRIGGER. Second, you can use the EXPLAIN command, as shown in
Listing 5-9.
Listing 5-9. Using EXPLAIN
mysql> explain plants;
+------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | char(50) | YES | | NULL | |
| location | char(30) | YES | | NULL | |
| plant_type | char(30) | YES | | NULL | |
+------------+----------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> explain readings;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | | NULL | |
| moisture | float | YES | | NULL | |
| temperature | float | YES | | NULL | |
| event_time | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| soil_status | enum('DRY', | YES | | NULL | |
| | 'OK','WET')| | | | |
+-------------+-------------+------+-----+-------------------+-----------------------------+
5 rows in set (0.00 sec)
Here you see a result set that shows all the columns and their data types and options for each table in
the plant monitoring database. Notice the extra information we get for the timestamp field. Here it tells us
the timestamp will be updated when the row is updated, but it also applies to a new row.
But wait! How does each sensor know what the ID is for each plant? Well, this is a bit of a cart-and-horse
situation. If you consider each sensor may be read and the data sent via a small microcontroller board, then
the code (sketch in Arduino lingo) must know the ID from the plants table. Since we used an AUTO_INCREMENT