Tables that use foreign keys (error 1005)

[Gareth Anderson]

I'm trying to create a few tables that store information for a mailing list.

Users table stores information on the users.

MailingLists stores information on the mailing lists themselves.

Subscribers links these two tables, stores info on which users are subscribed to which mailing list (they can be subscribed to zero or more than one).

The users table works like this:

create table users(
userID INT NOT NULL AUTO_INCREMENT,
emailAdd VARCHAR(60) NOT NULL,
LastName VARCHAR(50),
FirstName VARCHAR(50),
Password VARCHAR(20),
PRIMARY KEY (userID)) TYPE = INNODB;

The mailing lists table like this:

create table mailingLists(
listID INT NOT NULL AUTO_INCREMENT,
emailAdd VARCHAR(60) NOT NULL,
name VARCHAR(30) NOT NULL,
PRIMARY KEY (listID)) TYPE = INNODB;

The below table is the problem table, subscribers.

The idea is that if a user is subscribed to a mailing list then this mailing list then we need to have that data in this table.

Obviously the userid comes from the users table and the listID comes from the mailingLists table.

create table subscribers(
userID INT NOT NULL,
listID INT NOT NULL,
FOREIGN KEY (userID) REFERENCES users(userID) ON DELETE CASCADE,
FOREIGN KEY (listID) REFERENCES mailingLists(listID) ON DELETE CASCADE,
Primary Key (listID, userID)) TYPE = INNODB;

Unfortunately no matter what I do I cannot get it to work.

Any idea where I am going wrong here?

Any help appreciated,
Gareth

Note that after thinking I could avoid using foreign keys but thats not what I'd prefer. Ie. use application logic to make sure that data integrity is maintained but its so much easier (and likely more efficient) to use foreign keys...