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. 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/ At 07:20 PM 12/13/01 +0200, you wrote: >Content-Type: message/rfc822 >Content-Description: forwarded message >Content-Transfer-Encoding: 7bit > >MIME-Version: 1.0 >Content-Type: text/plain; charset=us-ascii >Subject: Bug in Foreign key constraint >From: Leonardo Dias <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Date: Thu, 13 Dec 2001 15:03:49 -0200 >Sender: [EMAIL PROTECTED] > >Hi there. > >I'm using MySQL-Max version 3.23.46 for Linux-i386 and I just found a >problem. > >It seems that every once in a while MySQL changes the references of a >foreign key when it's using temporary table. A show table status would >give us the following: > >InnoDB free: 21504 kB; (usr_id) REFER conline/#sql2-1a4-80a(usr_id); >(emp_id) REFER conline/emp(emp_id) > >And the table it should be referencing was the table usr. This names >looks particularly as a temporary table it changed for a while and >didn't change it back to normal after the transaction was ready. > > > >-- >Leonardo Dias >Catho Online >WebDeveloper >http://www.catho.com.br/ > >--------------------------------------------------------------------- >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 > > >Content-Type: text/plain; charset=us-ascii >Content-Description: message body and .signature >Content-Transfer-Encoding: 7bit > > > >Also something for you .. > >-- >Regards, > __ ___ ___ ____ __ > / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer >/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus > <___/ www.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
