Why not create a table in MySQL with an AUTO_INCREMENT?

Good day,
Read a tutorial on MySQL and if you run the examples from it, I can't create a new table with AUTO_INCREMENT'ohms. Code:
CREATE TABLE album (
artist_id SMALLINT(5) NOT NULL,
album_id SMALLINT(4) NOT NULL AUTO_INCREMENT,
album_name CHAR(128) DEFAULT NULL,
PRIMARY KEY (artist_id, album_id)
);

Error:
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

I understand the essence of what it swears, but I don't understand why? I have specified only one column with AUTO_INCREMENT and it is part of the index. In the book identical example works somehow. If you make the PRIMARY KEY only album_id - the table is created.
June 26th 19 at 14:22
2 answers
June 26th 19 at 14:24
Solution
In the book identical example works somehow

Magic hands and find the difference:
mysql> CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (artist_id, album_id) ) engine=innodb;
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
mysql> CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (artist_id, album_id) ) engine=myisam;
Query OK, 0 rows affected (0,00 sec)

mysql>

You have to book different default engines, in the future it still surprises arise. Very different myisam and innodb behavior, capabilities and limitations.
That's really so. =) Thank you. The book is old, 2007, describes how MySQL 5.0 (with frequent references to 4.1), I missed the point that to 5.5 by default, MyISAM is offered. - Tatum_Fu commented on June 26th 19 at 14:27
June 26th 19 at 14:26
Should be one field AUTO_INCREMENT , and it's one PRIMARY KEY
Yes, I understand. I do not understand why in the book the example works. I think that's a typo and it is correct to do so:
CREATE TABLE album ( artist_id SMALLINT(5) NOT NULL, album_id SMALLINT(4) NOT NULL AUTO_INCREMENT, album_name CHAR(128) DEFAULT NULL, PRIMARY KEY (album_id, artist_id) );

I.e. if you specify a composite key, the part that has the AUTO_INCREMENT properties must be specified first. At least it worked. - Tatum_Fu commented on June 26th 19 at 14:29
: apparently there's been a misprint - Tatum_Fu commented on June 26th 19 at 14:32

Find more questions by tags MySQL