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).