MySQL: Some Weirdness Going On

When I created the cdtable to show off Constraints, I had a little trouble. What everything I read in the manuals told me about defining them didn't work. The issue centred around the fields for which you were defining a foreign key constraint. The book told me to define a KEY for each field, but I found I had to define an INDEX.

Compare:

CREATE TABLE cddb.cdtable (
id INT NOT NULL AUTO_INCREMENT,
pubid INT NOT NULL,
artid INT NOT NULL,
title varchar(50) NOT NULL,
yearpublished INT,
PRIMARY KEY (`id`),
KEY `artid` (`artid`),
KEY `pubid` (`pubid`),
CONSTRAINT `cdtable_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`id`) ON DELETE CASCADE,
CONSTRAINT `cdtable_ibfk_2` FOREIGN KEY (`pubid`) REFERENCES `publisher` (`id`) ON DELETE CASCADE
) ENGINE=INNODB;

With

CREATE TABLE cddb.cdtable (
id INT NOT NULL AUTO_INCREMENT,
pubid INT NOT NULL,
artid INT NOT NULL,
title varchar(50) NOT NULL,
yearpublished INT,
PRIMARY KEY (id),
INDEX (artid),
FOREIGN KEY (artid)
REFERENCES artist(id) ON DELETE CASCADE,
INDEX (pubid),
FOREIGN KEY (pubid)
REFERENCES publisher(id) ON DELETE CASCADE
) ENGINE=INNODB;

The first is by the book, the second actually work!

Well, this morning I decided to back my database up and transfer it over to my study PC. I used the standard GUI backup tool, and 'lo and behold', the backup script it generated had the same issue:

--
-- Definition of table `cdtable`
--

DROP TABLE IF EXISTS `cdtable`;
CREATE TABLE `cdtable` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `pubid` int(11) NOT NULL,
  `artid` int(11) NOT NULL,
  `title` varchar(50) NOT NULL,
  `yearpublished` int(11) DEFAULT NULL,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `artid` (`artid`),
  KEY `pubid` (`pubid`),
  CONSTRAINT `cdtable_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`id`) ON DELETE CASCADE,
  CONSTRAINT `cdtable_ibfk_2` FOREIGN KEY (`pubid`) REFERENCES `publisher` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;

To allow the backup to restore I had to modify the code to reflect what I had originally written! Good job I'd blogged the code!