3

I'm trying to change a table's type to MyISAM but it won't let me:

ALTER TABLE `mytable` ENGINE = MYISAM

MySQL said:

Documentation 1217 - Cannot delete or update a parent row: a foreign key constraint fails

nickhar
  • 19,981
  • 12
  • 60
  • 73
user1701467
  • 285
  • 2
  • 7
  • 20

2 Answers2

1

MYISAM does not support foreign keys

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.

So you have to drop all foreign keys in order to use MYISAM. Example:

ALTER TABLE mytable DROP FOREIGN KEY key_name
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 1
    Oh what? Is there a table type that supports both? I have a table with foregin keys but I need to enforce FULLTEXT on one of its columns. – user1701467 Nov 05 '12 at 23:02
0

upgrade your mysql to 5.6.4 or higher.

with 5.6.4 mysql innodb has full text support.

http://dev.mysql.com/doc/refman/5.6/en/news-5-6-4.html

InnoDB Notes

MySQL now supports FULLTEXT indexes for InnoDB tables. The core syntax is very similar to the FULLTEXT capability from earlier releases, with the CREATE TABLE and CREATE INDEX statements, and MATCH() ... AGAINST() clause in the SELECT statement. The new @ operator allows proximity searches for terms that are near each other in the document. The detailed search processing is controlled by a new set of configuration options: innodb_ft_enable_stopword, innodb_ft_server_stopword_table, innodb_ft_user_stopword_table, innodb_ft_cache_size, innodb_ft_min_token_size, and innodb_ft_max_token_size. You can monitor the workings of the InnoDB full-text search system by querying new INFORMATION_SCHEMA tables: innodb_ft_default_stopword, innodb_ft_index_table, innodb_ft_index_cache, innodb_ft_config, innodb_ft_deleted, and innodb_ft_being_deleted.

Nesim Razon
  • 9,684
  • 3
  • 36
  • 48