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 . 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

On Thu, Mar 13, 2008 at 12:33 PM, Yves Goergen
<nospam.list@unclassified.de> wrote:

> 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" ADD FOREIGN KEY ("MessageId", "SearchRevision")
> REFERENCES "message_revision" ("MessageId", "RevisionNumber") ON DELETE
> CASCADE;
>

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


I am guessing it is an issue with "SearchRevision" being an INTEGER,
and "RevisionNumber" being a SMALLINT.

http://www.google.com/search?q=150+error+mysql yields
http://bugs.mysql.com/bug.php?id=6188 as it's first result.

"InnoDB does not give a clear error message if you try to create a
table with one or more
column types that do not match the column types of foriegn keys that
had been established
against that table."

--
Rob Wultsch

On Thu, Mar 13, 2008 at 7:55 PM, Rob Wultsch <wultsch@gmail.com> wrote:
> On Thu, Mar 13, 2008 at 12:33 PM, Yves Goergen
> <nospam.list@unclassified.de> wrote:
>
> > 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" ADD FOREIGN KEY ("MessageId", "SearchRevision")
> > REFERENCES "message_revision" ("MessageId", "RevisionNumber") ON DELETE
> > CASCADE;
> >
>
>
> > #1005 - Can't create table '.\unb2test\#sql-770_2.frm' (errno: 150)
>
>
> I am guessing it is an issue with "SearchRevision" being an INTEGER,
> and "RevisionNumber" being a SMALLINT.

Yes, that's what is causing the trouble. But that's not all: the
UNSIGNED attribute has to match too. (It does in this case, but I'm
just pointing it out for future reference).

On 14.03.2008 00:55 CE(S)T, Rob Wultsch wrote:
> I am guessing it is an issue with "SearchRevision" being an INTEGER,
> and "RevisionNumber" being a SMALLINT.

Thank you, that was the problem. My design was incorrect anyway to use
different types here... Now that's fixed, too.

> http://www.google.com/search?q=150+error+mysql yields
> http://bugs.mysql.com/bug.php?id=6188 as it's first result.

Not so in Germany. Also, I've searched for a more specific message.
Must have missed it.

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