SERIAL Columns for Indexes

    SERIAL is a column definition attribute that I use frequently and it caught me by surprise when someone looking at a slide deck of a past presentation of mine asked about my use of it. This person is a long time user of MySQL but did not know this keyword. So I need to spread the word about SERIAL. This might save you some typing and some related fat fingering.

Defining Your Index Column

    When creating a table you want a PRIMARY KEY column. And even with the SERIAL attribute you have to designate which column this is. You cannot use more than one SERIAL designated column or you will get ERROR: 1075: Incorrect table definition; there can be only one auto column and it must be defined as a key.  Why PRIMARY KEY is not added to the other good stuff include with SERIAL does not make sense to me but there are probably dozens of reasons plus the SQL standard arguing against me here.

    Most of the time INTEGERS are used as the data type for that key column. Having the system automatically increment the count for the column is not only handy but provides uniqueness, at least to some extent.  You want to avoid NULL values as they can cause a whole bucket of problems on an indexed field.  And it helps to use only positive values (up to 2^63 - 1).  And most of the time you have no idea how many records there will be so you use the BIGINT type just so that you should never run out of numbers (even though smaller indexes have some performance benefits you should be aware of).  

    So that means you have to specify an unsigned big integer, not nullable, auto incremented column. That is a lot of things to specify and I like to take shortcuts that make sense. That is why I like SERIAL.  In the following example it is easy to see that the six letter 'SERIAL' is a lot less typing than 'bigint unsigned NOT NULL AUTO_INCREMENT' string it encapsulates. 

 SQL > create table pp1 (id SERIAL PRIMARY KEY, doc_number INT UNSIGNED)\G
Query OK, 0 rows affected (0.0334 sec)
SQL > SHOW CREATE TABLE pp1\G
*************************** 1. row ***************************
       Table: pp1
Create Table: CREATE TABLE `pp1` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `doc_number` int unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.0049 sec)
SQL >

Anything that improves readability and reduces the opportunity for yours truly to fat finger is a big plus.