Hi all, i need help with this query, on mysql5:

ALTER TABLE `mreserv` ADD FOREIGN KEY ( `Acode` ) REFERENCES `Aclient`


it returns

errno: 1005
--------------------------------------------------------------------------------
error: Ne peut créer la table '.\test\#sql-a64_59.frm' (Errcode: 150)
(translat.: can't create table '...)
--------------------------------------------------------------------------------
mysql version: 5.0.18

You have to specify to which table you'd like to refer to, like so:

ALTER TABLE [table_name] ADD FOREIGN KEY ([column_name]) REFERENCES
[referenced_table_name] ([referenced_column_name])

Markus

"Markus Popp" <mfp@gmx.li> wrote in message
news:43b85b52$0$16890$91cee783@newsreader01.highwa y.telekom.at...
> You have to specify to which table you'd like to refer to, like so:
>
> ALTER TABLE [table_name] ADD FOREIGN KEY ([column_name]) REFERENCES
> [referenced_table_name] ([referenced_column_name])
>
> Markus
>
>

every time I try to add a foreign key to the table after a table's already
been created, I get an error message "MySQL error 1005. can't create table
'.\dbo\#sql-358_8.frm' (errno: 150)"
any clues? bug?

ALTER TABLE `dbo`.`quiz_reports_quiz_questions` ADD CONSTRAINT
`FK_quiz_reports_quiz_questions_1` FOREIGN KEY
`FK_quiz_reports_quiz_questions_1` (`quiz_id`) REFERENCES `quiz_quizzes`
(`quiz_id`);

MySQL Error Number 1005
Can't create table '.`dbo\#sql-358_1.frm' (errno: 150)

"Jim Michaels" <NOSPAMFORjmichae3@yahoo.com> wrote:
>
> every time I try to add a foreign key to the table after a table's already
> been created, I get an error message "MySQL error 1005. can't create table
> '.\dbo\#sql-358_8.frm' (errno: 150)"
> any clues? bug?

RTFM

Error 150 references to a situation where you create a foreign key
contraint without having suitable indexes on the respective columns.
Please note that you need those indexes in both tables. MySQL does
not create indexes automatically (as other RDBMS do).


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

"Axel Schwenke" <axel.schwenke@gmx.de> wrote in message
news:6et5vd.ulq.ln@idefix.xl.local...
> "Jim Michaels" <NOSPAMFORjmichae3@yahoo.com> wrote:
>>
>> every time I try to add a foreign key to the table after a table's
>> already
>> been created, I get an error message "MySQL error 1005. can't create
>> table
>> '.\dbo\#sql-358_8.frm' (errno: 150)"
>> any clues? bug?
>
> RTFM
> Error 150 references to a situation where you create a foreign key
> contraint without having suitable indexes on the respective columns.

Axel,

Can you supply a link to the relevant part of the documentation that
explains this as the cause for that errno 150? Before you tell users to
RTFM, you should be sure that the FM has the answer.

I found
http://dev.mysql.com/doc/refman/5.0/...nstraints.html
which says only that "if an ALTER TABLE fails and it refers to errno 150,
that means a foreign key definition would be incorrectly formed for the
altered table." That's close to the explanation you mentioned, but it's
unclear and suggests no remedy.

I found http://dev.mysql.com/doc/refman/5.0/...ror-codes.html which
has a similar statement, but no further help.

Also not especially helpful:

C:> perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

> Please note that you need those indexes in both tables. MySQL does
> not create indexes automatically (as other RDBMS do).

RTFM yourself Axel! ;-)

http://dev.mysql.com/doc/refman/4.1/...ews-4-1-2.html says:
"Changes in MySQL/InnoDB-4.1.2
.. . .
Automatically create a suitable index on a FOREIGN KEY, if the user does not
create one. Removes most of the cases of Error 1005 (errno 150) in table
creation."

The OP said he's using MySQL 5.0.18, so this change in 4.1.2 should still
apply.

And
http://dev.mysql.com/doc/refman/5.0/...nstraints.html
says:
"In the referencing table, there must be an index where the foreign key
columns are listed as the first columns in the same order. Such an index is
created on the referencing table automatically if it does not exist."

Finally, you must admit that the error message of "can't create table" is
nearly useless. Any error message should ideally tell the user the
following:

1. What happened (e.g. "can't create table")
2. Why it happened. ("referencing field(s) lacking index")
3. What to do to fix it. ("create index manually before creating foreign key
constraint")

Regards,
Bill K.

"Jim Michaels" <NOSPAMFORjmichae3@yahoo.com> wrote in message
news:T9idnS_-9qHJpYvZRVn-tA@comcast.com...
> every time I try to add a foreign key to the table after a table's already
> been created, I get an error message "MySQL error 1005. can't create table
> '.\dbo\#sql-358_8.frm' (errno: 150)"
> any clues? bug?

Several bugs appear with this errno symptom.
http://bugs.mysql.com/bug.php?id=8625
http://bugs.mysql.com/bug.php?id=10082
http://bugs.mysql.com/bug.php?id=10325
http://bugs.mysql.com/bug.php?id=15324

The usual suggestion is to use SHOW INNODB STATUS for more information.

MySQL versions prior to 4.1.2 do not create an index automatically when you
create a foreign key constraint. You need to create the index manually and
then create the constraint. Later versions of MySQL should create an index
on the referencing field(s) implicitly when you create the foreign key
constraint.

Note that the referenced column (e.g. quiz_quizzes.quiz_id) must be indexed
to be a valid referenced field. Also, if the referencing and referenced
fields are string fields, they must have identical lengths, and compatible
character sets and collations (it's best if they're both BINARY).

Regards,
Bill K.

"Bill Karwin" <bill@karwin.com> wrote:
> "Axel Schwenke" <axel.schwenke@gmx.de> wrote in message
>>
>> RTFM
>> Error 150 references to a situation where you create a foreign key
>> contraint without having suitable indexes on the respective columns.
>
> Can you supply a link to the relevant part of the documentation that
> explains this as the cause for that errno 150? Before you tell users to
> RTFM, you should be sure that the FM has the answer.
>
> I found
> http://dev.mysql.com/doc/refman/5.0/...nstraints.html
> which says only that "if an ALTER TABLE fails and it refers to errno 150,
> that means a foreign key definition would be incorrectly formed for the
> altered table." That's close to the explanation you mentioned, but it's
> unclear and suggests no remedy.

Bill,

you stopped quoting the manual just before the most important sentence:

"Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display
a detailed explanation of the latest InnoDB foreign key error in the
server."


Example:

mysql> alter table t2 add constraint foreign key (c1) references t1 (c1);
ERROR 1005 (HY000): Can't create table './test/#sql-2856_1.frm' (errno: 150)
mysql> show innodb status;
....
------------------------
LATEST FOREIGN KEY ERROR
------------------------
060315 9:27:44 Error in foreign key constraint of table test/#sql-2856_1:
foreign key (c1) references t1 (c1):
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.

>> Please note that you need those indexes in both tables. MySQL does
>> not create indexes automatically (as other RDBMS do).
>
> RTFM yourself Axel! ;-)

Got me :-)

I correct myself: "InnoDB does not create an index in the referenced
table automatically."

> Finally, you must admit that the error message of "can't create table" is
> nearly useless. Any error message should ideally tell the user the
> following:
>
> 1. What happened (e.g. "can't create table")
> 2. Why it happened. ("referencing field(s) lacking index")
> 3. What to do to fix it. ("create index manually before creating foreign key
> constraint")

Well, the problem here is, that maintaining foreign key constraints
is delegated to the storage engine (and it's no-ops in all SE except
InnoDB). Furthermore, ALTER TABLE is implemented as CREATE (new) TABLE,
INSERT INTO (new) SELECT FROM (old), RENAME TABLE (new) TO (old).
Therefor you get an error for CREATE TABLE although you did ALTER TABLE

After all the best advise to recover from error 150 is to use SHOW
INNODB STATUS, because all the "interesting" things are happening
inside InnoDB. In praxis error 150 almost every time refers to a
missing index. Probably the manual should contain a note on it.


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

"Axel Schwenke" <axel.schwenke@gmx.de> wrote in message
news:1nk8vd.idu.ln@idefix.xl.local...
> you stopped quoting the manual just before the most important sentence:
>
> "Starting from MySQL 4.0.13, you can use SHOW INNODB STATUS to display
> a detailed explanation of the latest InnoDB foreign key error in the
> server."

Excellent! That's good advice for folks using InnoDB.

> Well, the problem here is, that maintaining foreign key constraints
> is delegated to the storage engine (and it's no-ops in all SE except
> InnoDB).

Hmm. Then the pluggable SE architecture should provide some way of
delegating error message generation. That is, the SE should be able to
"bubble up" an error message and report an error seamlessly through the
standard MySQL error reporting. Requiring the use of a SE-specific
statement to get full error details makes it non-obvious for someone writing
a client application to get appropriate error details, unless they are also
the database architect. They at least have to know what storage engine is
being used for a given table, which may be a detail outside the knowledge of
an application developer. Also, access to the errors may be further
complicated by using client interfaces, e.g. Hibernate.

Anyway, thanks for the clarifications.

Regards,
Bill K.