3

I'm working on a plugin and have an activation hook that creates several tables using dbDelta. The tables all get created without any errors or issues. Several of the tables have foreign key constraints, which all also work fine.

However, if I deactivate and reactive the plugin, the last constraint on each table throws the following:

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 'CONSTRAINT `fk_fkName` FOREIGN KEY (`Sour' at line 1 for query ALTER TABLE wp_table ADD COLUMN CONSTRAINT `fk_fkName` FOREIGN KEY (`Source`) REFERENCES wp_other_table (`SourceID`) ON DELETE RESTRICT ON UPDATE RESTRICT

The function that is called is:

public static function create_table() { $sql = "CREATE TABLE " . self::$tbl_table . " ( `CustomerID` int NOT NULL AUTO_INCREMENT, `CompanyName` varchar(50) DEFAULT NULL, `FirstName` varchar(50) NOT NULL, `LastName` varchar(50) NOT NULL, `AddressStreet` varchar(50) NOT NULL, `AddressSecond` varchar(50) DEFAULT NULL, `AddressCity` varchar(50) NOT NULL, `AddressZip` varchar(10) NOT NULL, `PhoneNumber` varchar(13) DEFAULT NULL, `EmailAddress` varchar(50) DEFAULT NULL, `Commercial` bit(1) NOT NULL DEFAULT b'0', `Source` int NOT NULL, `DoNotTake` bit(1) NOT NULL DEFAULT b'0', `Preferred` bit(1) NOT NULL DEFAULT b'0', `Notes` varchar(300) DEFAULT NULL, PRIMARY KEY (`CustomerID`), CONSTRAINT `fk_fkName` FOREIGN KEY (`Source`) REFERENCES " . self::$tbl_other_table . " (`SourceID`) ON DELETE RESTRICT ON UPDATE RESTRICT ) ENGINE=InnoDB;"; dbDelta($sql); }

*table names are obfuscated out of habit

In that particular table, there's only the one constraint, and it throws the error. In other tables there are as few as two and as many as five. It's always an error on the last constraint, and only on activations after the table was first created.

Things I've tried:

  • Adding and removing whitespace around SQL keywords
  • Adding a comma after the last constraint (it was the only actual difference between constraints that complain and those that don't)

Here's another example with multiple fk constraints:

CONSTRAINT `fk_fk1` FOREIGN KEY (`Field1`) REFERENCES " . self::$tbl_t1 . " (`Field1`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_fk2` FOREIGN KEY (`Field2`) REFERENCES " . self::$tbl_t2 . " (`Field2`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_fk3` FOREIGN KEY (`Field3`) REFERENCES " . self::$tbl_t3 . " (`Field3`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_fk4` FOREIGN KEY (`Field4`) REFERENCES " . self::$tbl_t4 . " (`Field4`) ON DELETE RESTRICT ON UPDATE RESTRICT, CONSTRAINT `fk_fk5` FOREIGN KEY (`Field5`) REFERENCES " . self::$tbl_t5 . " (`Field5`) ON DELETE RESTRICT ON UPDATE RESTRICT

*again, table names and key names are obfuscated.

Just to reiterate, the tables all create fine on the initial activation with all constraints in place. In the first example, the single constraint throws the error for that table when activation/update occurs after the first time (deactivate/activate, update, anything that calls the activation hook). In the second example (and others), it's only the last constraint that throws the error, and again only after the table already exists and the activation hook is called later.

Since the constraint already exists, dbDelta should be ignoring it after comparing the table schema, but for some reason it's not.

Improve this question

1 Answer 1

Reset to default
6

dbDelta() doesn’t properly parse or compare foreign key constraints — it’s built to detect differences in columns, indexes, and primary keys, but it doesn’t have full logic to recognise existing CONSTRAINT … FOREIGN KEY clauses.

The result is that on the second activation it sees your CONSTRAINT line as “missing” and tries to run something like:

ALTER TABLE wp_table ADD COLUMN CONSTRAINT `fk_fkName` FOREIGN KEY ...

…which is invalid SQL (ADD COLUMN with a CONSTRAINT clause), hence the syntax error you’re seeing. The reason it’s always the last constraint that errors is because of how dbDelta() parses the CREATE TABLE statement — it ignores the trailing comma rules for constraints and the last one ends up getting mis‑tokenised.

Options to fix / work around

1. Remove constraints from dbDelta() SQL

Let dbDelta() create columns and indexes only, and then add your foreign keys separately:

dbDelta( $create_table_sql ); // Then add constraints manually $wpdb->query( "ALTER TABLE {$tbl} ADD CONSTRAINT fk_fkName FOREIGN KEY (Source) REFERENCES {$tbl_other} (SourceID) ON DELETE RESTRICT ON UPDATE RESTRICT " );

Since you control these queries, you can check INFORMATION_SCHEMA.TABLE_CONSTRAINTS or MySQL’s SHOW CREATE TABLE result to skip adding if it already exists.

2. Add fake indexes for dbDelta() comparison

Because dbDelta() knows about indexes, you can:

  • Put the FOREIGN KEY in a second ALTER (as above)
  • Add an INDEX Source (Source) so dbDelta() still creates necessary indexes, and your FK is handled externally.

3. Maintain your own schema sync

If you require full FK management, skip dbDelta() entirely and run proper CREATE TABLE IF NOT EXISTS / ALTER TABLE … ADD CONSTRAINT statements yourself during activation. This avoids dbDelta() parsing limitations altogether.

Why this happens

  • dbDelta() was originally built to support WordPress core schema (MyISAM, no foreign keys).
  • Its regex parsing is brittle and doesn’t understand FOREIGN KEY constraints properly.
  • On subsequent runs it tries to issue broken ALTER TABLE ADD COLUMN CONSTRAINT… statements.

Reference: dbDelta limitations in core trac ticket #20263 — foreign key handling is not supported natively.

Summary:
dbDelta() can create tables with FKs the first time, but will not skip them gracefully on later runs. The robust pattern is to let dbDelta() handle the column/index definitions and run separate guarded ALTER TABLE statements for foreign keys.

Improve this answer
1
  • 1
    Ahmad - thank you for the excellent answer! I'll move all of the constraints into a separate function. Commented Aug 18 at 15:49

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.