I have the following sql I am using with dbDelta:
$sql .= "CREATE TABLE " . $location_table . " ( location_id MEDIUMINT(9) NOT NULL AUTO_INCREMENT, name VARCHAR (100), street_no VARCHAR (5), street_name VARCHAR (75), city VARCHAR (75), province_state VARCHAR (75), postal_code VARCHAR(10), country VARCHAR (75), post_page_url VARCHAR(300), icon_id MEDIUMINT(9), PRIMARY KEY (location_id), FOREIGN KEY (icon_id) REFERENCES ".$wpdb->prefix."nc_icon (icon_id) );";
I am getting the 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 (icon_id) REFERENCES wp_nc_icon (icon_id)' at line 1] ALTER TABLE wp_nc_location ADD COLUMN FOREIGN KEY (icon_id) REFERENCES wp_nc_icon (icon_id)
It seems like instead of writing:
ALTER TABLE wp_nc_location ADD CONSTRAINT FOREIGN KEY (icon_id) REFERENCES wp_nc_icon (icon_id)
DBDelta is trying to write:
ALTER TABLE wp_nc_location ADD COLUMN FOREIGN KEY (icon_id) REFERENCES wp_nc_icon (icon_id)
However, it looks like the column icon_id is being created, which i all I need.
Actually it’s weird because icon_id is also a foreign key (when I look at the table structure).
I just want to get rid of my “unexpected output” error. Is there a way to make DBDelta accept a foreign key?
As a general rule, dbDelta does not yet support FOREIGN KEY, though I’ve been told it works on MySQL 5.1 (I can confirm it doesnt work on MySQL 5.5).