I create a table in WordPress database with this SQL query:

CREATE TABLE IF NOT EXISTS wp_ccwwhsh_sent_messages(
    `id` INT NOT NULL AUTO_INCREMENT,
    `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `recipient_id` BIGINT NOT NULL,
    `phone` VARCHAR(15) NOT NULL,
    `content` TEXT NOT NULL,
    `sending_confirm` DATETIME NULL,
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

and it works. If a try to create a new tabled linked to the first one with:

CREATE TABLE IF NOT EXISTS wp_ccwwhsh_replies_to_messages(
    `id` INT NOT NULL AUTO_INCREMENT,
    `timestamp` DATETIME NOT NULL,
    `message_id` BIGINT NOT NULL,
    `content` TEXT NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`message_id`) REFERENCES wp_ccwwhsh_sent_messages(`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I get error #1005 - Impossible to create the table wordpress-dev.wp_ccwwhsh_replies_to_messages (errno: 150 "Foreign key constraint is incorrectly formed"), what's wrong?

I create a table in WordPress database with this SQL query:

CREATE TABLE IF NOT EXISTS wp_ccwwhsh_sent_messages(
    `id` INT NOT NULL AUTO_INCREMENT,
    `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `recipient_id` BIGINT NOT NULL,
    `phone` VARCHAR(15) NOT NULL,
    `content` TEXT NOT NULL,
    `sending_confirm` DATETIME NULL,
    PRIMARY KEY(`id`)
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

and it works. If a try to create a new tabled linked to the first one with:

CREATE TABLE IF NOT EXISTS wp_ccwwhsh_replies_to_messages(
    `id` INT NOT NULL AUTO_INCREMENT,
    `timestamp` DATETIME NOT NULL,
    `message_id` BIGINT NOT NULL,
    `content` TEXT NOT NULL,
    PRIMARY KEY(`id`),
    FOREIGN KEY(`message_id`) REFERENCES wp_ccwwhsh_sent_messages(`id`) ON DELETE CASCADE
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

I get error #1005 - Impossible to create the table wordpress-dev.wp_ccwwhsh_replies_to_messages (errno: 150 "Foreign key constraint is incorrectly formed"), what's wrong?

• plugins
• database
• query
• mysql

1 Answer 1

Your message_id is a BIGINT whereas the id of wp_ccwwhsh_sent_messages is INT. Replace BIGINT by INT and try again.

The error message, unfortunately, isn't very helpful.