DB table creation on reactivation – dbDelta errors on CONSTRAINTS

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.