I am developing a plug-in which is creating additional database tables. The plug-in is creating tables just fine using the dbDelat($sql) function. The issue is when I want to "update" the existing table structure.
Here is a sample code I use to create a table
.... $sql = "CREATE TABLE wp_test_me id bigint(5) UNSIGNED NOT NULL AUTO_INCREMENT, fk_id bigint(5) UNSIGNED NOT NULL, description text NOT NULL, PRIMARY KEY (id), FOREIGN KEY (fk_id) REFERENCE other_table(ID) );" require_once (ABSPATH . 'wp-admin/includes/upgrade.php'); dbDelta( $sql ); ...
As mentioned, the first time around when I execute this code, it creates the table and the foreign keys just fine.
When I modify the structure (e.g. adding a new field) and run the script again to update the DB, it does add the new field successfully, yet fails at the FOREIGN KEY with the following error:
WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOREIGN KEY (fk_id) REFERENCES other_table(ID)' at line 1] ALTER TABLE wp_test_me ADD COLUMN FOREIGN KEY (fk_id) REFERENCES other_table(ID)
My understanding is that dbDelta() realizes that the table structure has changed and is invoking the "ALTER TABLE..." functionality on the database table. When I cut and paste the ALTER TABLE SQL statement into MySQL, I get the same error; obviously.
Here is the kicker - When I remove change the SQL statement within MySQL to
ALTER TABLE wp_test_me ADD FOREIGN KEY (fk_id) REFERENCES other_table(ID)
(without the COLUMN key word), MySQL accepts the command and alters the table structure accordingly.
I also tried to remove the FOREIGN KEY if it already exists before updating, yet the dbDelta() function still invokes the incompatible ALTER TABLE command to add it again.
I'd hate to modify the core PHP files to remove the WordPress generated ALTER TABLE command, since I'd have to remember changing it after each upgrade.
Am I missing something obvious to make this work?