Foreign Key Issue. Driving me nuts...

I have this one associative table which MySQL Does not like (getting an errno 150). I have no idea why there is an issue with the foreign key. I have been trying to find the problems for some time, but with no luck. Can anyone spot any problems with the foreign key statements in the MovieActor table? (BTW, I am using MySQLCC to run the SQL statements, so might there be an issue there ? )

CREATE TABLE Gaurav.Movie (
MovieID int(10) unsigned NOT NULL auto_increment,
CategoryID int(10) unsigned NOT NULL,
MovieName char(100) NOT NULL,
MovieType ENUM('VHS', 'DVD') NOT NULL default 'VHS',
RentalDuration int(10) unsigned NOT NULL,
DisplayWeb ENUM('TRUE', 'FALSE') NOT NULL default 'FALSE',
Producer char(100),
Director char(100),
Language ENUM('Hindi', 'Punjabi', 'Gujrati', 'Urdu', 'Other') NOT NULL,
ReleaseDate DATE,
Rated ENUM('G','R') NOT NULL default 'G',
ArrivalDate DATE,

PRIMARY KEY (MovieID),
FOREIGN KEY (CategoryID) REFERENCES Gaurav.MovieCategory(CategoryID)
ON UPDATE CASCADE ON DELETE RESTRICT,
UNIQUE (CategoryID),
UNIQUE (MovieID)
) TYPE=InnoDB;

CREATE TABLE Gaurav.Actor (
ActorID int(10) unsigned NOT NULL auto_increment,
FirstName char(100) NOT NULL default '',
LastName char(100) NOT NULL default '',
DateOfBirth DATE,

PRIMARY KEY (ActorID),
UNIQUE (ACtorID)
) TYPE=InnoDB;

CREATE TABLE Gaurav.MovieActor (
ActorID int(10) unsigned NOT NULL,
MovieID int(10) unsigned NOT NULL,

PRIMARY KEY (ActorID, MovieID),
#UNIQUE (ActorID, MovieID),
FOREIGN KEY (ActorID) REFERENCES Gaurav.Actor(ActorID)
ON UPDATE CASCADE ON DELETE RESTRICT,
FOREIGN KEY (MovieID) REFERENCES Gaurav.Movie(MovieID)
ON UPDATE CASCADE ON DELETE RESTRICT
) TYPE=InnoDB;

** PROBLEM LIES WITH THE 2nd foreign key statement in MovieActor table. **

Any help will be appreciated.

Maybe it has something to do with the fact that both the MovieActor and the Movie tables have a key named "MovieID." To test it out, try renaming one of the fields and adjusting the foreign key as needed.

Okay, ill try that out, but im curious as to why the foreign key reference to ActorID works fine. If I comment out the second reference there are no errors.

No, changing the names of the fields in the MovieActor table so that they are different from the name of the keys they reference doesnt help. Still getting the same referential integrity problem.

I noticed something else. The foreign key reference to Actor(ActorID) is fine, while the one to Movie(MovieID) is not. That happens then the PK and uniques are defined in the table as follows:

...
PRIMARY KEY (ActorID, MovieID),
UNIQUE (ActorID, MovieID),
...

But if you switch around the order of the two fields in these statements so that they read:

...
PRIMARY KEY (MovieID, ActorID),
UNIQUE (MovieID, ActorID),
...

Then all of a sudden the MovieID foreign reference is fine, and the ActorID one now is causing the issue...

Duh, shoulda noticed that you were defining two primary keys. By definition, there can be only one, right? That explains why it's always the second one that gives you the error.

Nope, theres only 1 primary key. Its a concatenation of the two foreign keys since it needs to be unique. So thats not the problem. I did find a method that made it work though. The following creates the table with no problems:

CREATE TABLE Gaurav.MovieActor (
ActorID int(10) unsigned NOT NULL references Actor(ActorID) on update cascade on delete restrict,
MovieID int(10) unsigned NOT NULL references Movie(MovieID) on update cascade on delete cascade,
PRIMARY KEY (ActorID, MovieID),
UNIQUE (ActorID, MovieID)
) TYPE=InnoDB;

It would appear that as long as you put the references and the integrities in the same declaration as the fields themselves, there is no problem. Thx for the help guys.