I would first see if an upgrade to a later version of InnoDB tables is possible. What is being written to the error log? The ALTER TABLE statement subtly creates a new table, with new contraint names that the child table is unaware of, and drops the original table. Have you tried rebuilding the child table?
-----Original Message----- From: Rich Schramm To: [EMAIL PROTECTED] Sent: 5/18/04 12:43 PM Subject: Alter table primary key and foreign keys I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ****************************** bom_mstr primary key(bom_id) ****************************** ****************************** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ****************************** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
