How can I change the character set in an InnoDB table using foreign keys?
I have a set of InnoDB tables with foreign keys, I want to change the default character set from this table from latin1_swedish_ci
to utf8_general_ci
, I tried below query:
ALTER TABLE test.PAYMENT DROP FOREIGN KEY PAYMENT_ibfk_1;
But it gives me this error:
ERROR 1025 (HY000): Error on rename of './test/#sql-1fa_24f43' to './test/PAYMENT' (errno: 150)
I tried to accomplish this with set foreign_key_checks=0
; and even disabling the keys, but still get the same error.
+3
source to share
1 answer
I resolved it by removing foreign keys from all referenced tables and main tables
alter table test.PAYMENT drop foreign key PAYMENT_ibfk_1;
alter table test.ORDER_DETAILS drop foreign key ordr_dets_ibfk_1;
alter table test.TRANSACTION drop foreign key transaction_ibfk_1;
Then subsequently all tables were changed and the character set changed
alter table test.PAYMENT CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
alter table test.TRANSACTION CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
alter table test.ORDER_DETAILS CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
then foreign keys are applied again
alter table niclient.PAYMENT add foreign key PAYMENT_ibfk_1 (paymentTransId) references `TRANSACTION` (`transId`) ON DELETE CASCADE;
alter table niclient.ORDER_DETAILS add foreign key ordr_dets_ibfk_1 (ordrDetTransId) REFERENCES `TRANSACTION` (`transId`) ON DELETE CASCADE;
alter table niclient.TRANSACTION add foreign key transaction_ibfk_1 (transCompId) REFERENCES `COMPANY_DETAILS` (`compId`) ON DELETE CASCADE;
It worked fine fine :-)
+2
source to share