Hello,

I'm using MySQL 5.0 on Windows XP, with a few InnoDB tables and would
like to create a new foreign key constraint to one table.

Here's a simplified structure:

CREATE TABLE "message" (
"MessageId" INTEGER UNSIGNED NOT NULL PRIMARY KEY,
"Owner" INTEGER UNSIGNED NOT NULL,
"SearchRevision" INTEGER UNSIGNED)
ENGINE 'InnoDB' CHARACTER SET 'utf8' COLLATE 'utf8_bin';

CREATE TABLE "message_revision" (
"MessageId" INTEGER UNSIGNED NOT NULL,
"RevisionNumber" SMALLINT UNSIGNED NOT NULL,
"Author" INTEGER UNSIGNED NOT NULL,
PRIMARY KEY ("MessageId", "RevisionNumber"))
ENGINE 'InnoDB' CHARACTER SET 'utf8' COLLATE 'utf8_bin';

ALTER TABLE "message_revision" ADD FOREIGN KEY ("MessageId") REFERENCES
"message" ("MessageId") ON DELETE CASCADE;

This is all fine and I've put some data in the tables already. But all
data is valid and won't interfer with the following new constraint:

ALTER TABLE "message" ADD FOREIGN KEY ("MessageId", "SearchRevision")
REFERENCES "message_revision" ("MessageId", "RevisionNumber") ON DELETE
CASCADE;

Now the last command produces this error:

#1005 - Can't create table '.\unb2test\#sql-770_2.frm' (errno: 150)

I've restarted the MySQL service but it doesn't help. Is it broken? Why
is it trying to create some random table and why does that fail?

--
Yves Goergen "LonelyPixel" <nospam.list@unclassified.de>
Visit my web laboratory at http://beta.unclassified.de