Custom table schema cannot be updated

[over-engineer]

Rather than directly executing an SQL query to update our custom table’s schema, we utilize dbDelta(), which examines the current table structure, compares it to the desired table structure, and either adds or modifies the table as necessary.

There are some flaws in our current implementation.

According to the Codex, dbDelta() is rather picky:

• You must put each field on its own line in your SQL statement.
• You must have two spaces between the words PRIMARY KEY and the definition of your primary key.
• You must use the key word KEY rather than its synonym INDEX and you must include at least one KEY.
• KEY must be followed by a SINGLE SPACE then the key name then a space then open parenthesis with the field name then a closed parenthesis.
• You must not use any apostrophes or backticks around field names.
• Field types must be all lowercase.
• SQL keywords, like CREATE TABLE and UPDATE, must be uppercase.
• You must specify the length of all fields that accept a length parameter. int(11), for example.

So, we have to be mindful of those caveats.

Our database abstraction layer currently generates this SQL:

CREATE TABLE wp_pressidium_cookie_consents (
  id VARCHAR(40) NOT NULL PRIMARY KEY ,

We need to adjust it to generate SQL that looks like this:

CREATE TABLE wp_pressidium_cookie_consents (
  id VARCHAR(40) NOT NULL ,
/* … */
PRIMARY KEY  (id)

[over-engineer]

This was addressed in 1.5.1. Closing as resolved.