Hi!
At 04:10 PM 12/13/01 -0200, you wrote:
>Heikki Tuuri wrote:
>>
>> Hi!
>>
>> Foreign keys do not work if you do an ALTER TABLE. I have to add a note to
>> the manual that they do not work even if you do an ALTER TABLE to the
>> referenced table.
>>
>> Workaround: use DROP TABLE + CREATE TABLE to alter your table, whether it is
>> the referring table or the referenced table.
>>
>> If you do an ALTER TABLE to the referring table, then InnoDB drops all
>> foreign key constraints in it. But I forgot to add a note that an ALTER
>> TABLE in the referenced table also causes problems, because ALTER TABLE
>> actually renames the table during the process.
>
>That's not the case. We have never executed ALTER TABLE in that table.
>It just changed the reference to that #sql name suddenly. This caused
>problem to us, since that table no longer existed and we couldn't insert
>fields in the "child" table anymore.
>
>It has happened two times and we're only using it as a test environment.
>I want to make it clear that foreign key constraints are working before
>we put it on production. Or else I'd have to do it without foreign keys,
>which I think it's not a good idea.
Please try to find a repeatable test case.
I just tried ALTER TABLE to a referenced table and got the following:
| child1 | InnoDB | Fixed | 2 | 8192 | 16384 |
NULL | 16384 | 0 | NULL | NULL
| NULL | NULL | | InnoDB free: 136192 kB;
(a) REFER test/#sql2-6f0a-1(a); (b) REFER test/parent2(b) |
It looks a lot like the problem you reported.
>--
>Leonardo Dias
>Catho Online
>WebDeveloper
>http://www.catho.com.br/
Regards,
Heikki
Innobase Oy
---
See http://www.innodb.com for the latest news about InnoDB
Order commercial MySQL/InnoDB support at https://order.mysql.com/
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
