I got help from the mysql list. I'll paste Gabriel Preda's mail at the
end of this message.
Shortly, the fix is to add a DROP to the ALTER statement:
DROP FOREIGN KEY dbmail_messageblks_ibfk_1
the name might vary tho, as Gabriel says, although mine was the same as his
So the fully correct ALTER is this, or at least for me:
"
ALTER TABLE dbmail_messageblks
DROP FOREIGN KEY dbmail_messageblks_ibfk_1,
DROP INDEX messageblk_idnr,
DROP INDEX messageblk_idnr_2,
DROP INDEX msg_index,
CHANGE message_idnr physmessage_id bigint(21) NOT NULL DEFAULT '0',
ADD COLUMN is_header tinyint(1) DEFAULT '0' NOT NULL,
ADD INDEX physmessage_id_index (physmessage_id),
ADD INDEX physmessage_id_is_header_index (physmessage_id,
is_header),
ADD FOREIGN KEY physmessage_id_fk (physmessage_id)
REFERENCES dbmail_physmessage (id)
ON DELETE CASCADE ON UPDATE CASCADE;
"
His mail:
When creating the InnoDB table the InnoDB engine asigns to the FOREIGN
KEY you defined a symbol.
On my server it generated "dbmail_messageblks_ibfk_1"... and if in the
ALTER statement I entered:
DROP FOREIGN KEY dbmail_messageblks_ibfk_1
Then the ALTER table worked fine...
If you want to continue with this you should add a symbol name
manually like this in the create table statement:
CONSTRAINT `fk_message_idnr_manually_set` FOREIGN KEY
(`message_idnr`) REFERENCES `messages` (`message_idnr`)
Now in the ALTER statement you will have to write before you change
the name of the column:
DROP FOREIGN KEY `fk_message_idnr_manually_set`
If you DROP an index a FOREIGN KEY based on that index will not be
dropped automaticaly...
Hope this helps !
--
Gabriel PREDA
Senior Web Developer
