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?

  • Just like dbDelta wants 2 spaces after PRIMARY KEY, I think it wants 2 spaces after FOREIGN KEY (your CREATE TABLE shows with just 1 space, fwtw). But for me even after doing that and it creates the table fine, it seems like dbDelta is incorrectly detecting that the table has changed when the script is run again and issuing an ALTER TABLE which fails this way.
    – jfklein
    Nov 1, 2011 at 3:49
  • how did you make it work? Jan 23, 2014 at 22:31

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

  • I am on 5.5.29 and it doesn't work. I have the same problem as the OP. Feb 23, 2013 at 0:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.