Problems with DBDelta with FOREIGN key?

by

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?

1 Answer
1

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

Leave a Comment