Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

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?

share|improve this question
    
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 '11 at 3:49
    
how did you make it work? –  Francisco Corrales Morales Jan 23 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).

share|improve this answer
    
I am on 5.5.29 and it doesn't work. I have the same problem as the OP. –  Tyler Collier Feb 23 '13 at 0:53

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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