Foreign Key Contraints in MySQL

Schema

OK, these are a few tables to show off foreign key constraints:

CREATE TABLE cddb.publisher (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(20) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE cddb.artist (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

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;

mysql> insert into cdtable (pubid, artid, title, yearpublished)
-> values
-> (1,2,'Waking Up',2007);
Query OK, 1 row affected (0.01 sec)

mysql>
mysql> insert into cdtable (pubid, artid, title, yearpublished)
-> values
-> (1,3,'Yesterday, today & forever',2006);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`cddb`.`cdtable`, CONSTRAINT `cdta
ble_ibfk_1` FOREIGN KEY (`artid`) REFERENCES `artist` (`id`) ON DELETE CASCADE)
mysql>

As you can see, I could not add 'Yesterday, Today and Forever' as Vicky Beeching has not yet been added to the artists table.

Likewise, if I delete Bethany Dillon as an artist on the system, it will delete all her CDs as well.

mysql> select * from cdtable;
+----+-------+-------+-----------+---------------+
| id | pubid | artid | title     | yearpublished |
+----+-------+-------+-----------+---------------+
|  1 |     1 |     2 | Waking Up |          2007 |
+----+-------+-------+-----------+---------------+
1 row in set (0.00 sec)


mysql> delete from artist where id=2;
Query OK, 1 row affected (0.00 sec)

mysql> select * from cdtable;
Empty set (0.00 sec)

mysql>