plugins - Always get <errno: 150 "Foreign key constraint is incorrectly formed"> creating a new

admin管理员组

文章数量:1321251

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

Share Improve this question asked Sep 29, 2020 at 10:01 icolumbroicolumbro 7911 silver badge99 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 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.

本文标签：