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?
1 Answer
Reset to default 1Your 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.