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" <[EMAIL PROTECTED]>
Visit my web laboratory at http://beta.unclassified.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
