errno 150: Foreign key constraint is incorrectly formed

Question

I've read a fair few questions on this error and I either didn't understand what was happening/the answer, or it didn't fit my case.

I've got a simple user table already:

CREATE TABLE user
(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    password VARCHAR(64) NOT NULL
);
CREATE UNIQUE INDEX user_username_uindex ON user (username);

and I want to add a post table that references the user id:

CREATE TABLE post
(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    authorID INT NOT NULL,
    imgPath VARCHAR(500),
    postText TEXT NOT NULL,
    CONSTRAINT post_user_id_fk FOREIGN KEY (authorID) REFERENCES user (id) ON DELETE SET NULL
);

However the second table doesn't get created and instead throws the error mentioned in the title:

"Foreign key constraint is incorrectly formed".

I tried a few things that other questions' answers had mentioned (that I may have misunderstood), such as making user.id unique as well, though that did not work.

Could someone care to help me out and explain what the problem is. I've had more complex databases use this method and it's never thrown an error before.

Answer 1

In the current case, authorID NOT NULL was the problem but sometimes it is not easy to point what cause the error. There is an easy way to find what causes this error you can use: SHOW ENGINE INNODB STATUS;

This will show more details on error under section: "LATEST FOREIGN KEY ERROR".

More details here:

https://mariadb.org/mariadb-innodb-foreign-key-constraint-errors/

Answer 2

You need to remove NOT NULL from authorID:

CREATE TABLE post
(
    id INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    title VARCHAR(200) NOT NULL,
    date DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
    authorID INT ,   -- here NOT NULL was removed
    imgPath VARCHAR(500),
    postText TEXT NOT NULL,
    CONSTRAINT post_user_id_fk FOREIGN KEY (authorID) 
     REFERENCES user (id)  ON DELETE SET NULL
);

DBFiddle Demo

ON DELETE SET NULL and NOT NULL column are not compatible.