Can not add foreign key constraint when creating new tables

Question

I'am creating a very new DB and i am getting this error when i try to add some foreign key to my tables : Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Here are my codes :

$pdo->exec("CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    username VARCHAR(255) NOT NULL,
    password CHAR(255) NOT NULL,
    slug VARCHAR(255) NOT NULL,
    ft_img VARCHAR(255) NOT NULL,
    content text NOT NULL,
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(255) NOT NULL,
    role ENUM ('Author', 'Admin', 'Suscriber') NULL DEFAULT 'Suscriber',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

//Create posts table
$pdo->exec("CREATE TABLE posts (
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    user_id int NULL,
    title VARCHAR(255) NOT NULL,
    chapo VARCHAR(255) NOT NULL,
    ft_img VARCHAR(255) NOT NULL,
    content text NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    published TINYINT NOT NULL,
    FOREIGN KEY (user_id) REFERENCES USERS (id) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

//Create comments table
$pdo->exec("CREATE TABLE comments (
    id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
    pseudo VARCHAR(255) NOT NULL,
    title VARCHAR(255) NOT NULL,
    content text NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    published TINYINT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");


// Create posts_comments table
$pdo->exec("CREATE TABLE posts_comments (
    post_id INT UNSIGNED NOT NULL,
    comment_id INT UNSIGNED NOT NULL,
    PRIMARY KEY (post_id, comment_id),
    CONSTRAINT fk_post
        FOREIGN KEY (post_id)
        REFERENCES posts (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT,
    CONSTRAINT fk_comment
        FOREIGN KEY (comment_id)
        REFERENCES comments (id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4");

Error message : Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

I just can not figure out why i am getting this erro, if someone can help ?

Thanks in advance,

You have `posts` referencing `users` but there is no `users` table. You can't make a foreign key reference a table that hasn't been created yet. By analogy, you can't save a document file in a folder before you create that folder. — Bill Karwin, May 11 '21 at 15:53
@BillKarwin hey sorry, i just forgot to copy it on my question, i actually do have this table. — Thierry, May 11 '21 at 15:59

score 1 · Accepted Answer · answered May 11 '21 at 16:17

post_id INT UNSIGNED NOT NULL,
comment_id INT UNSIGNED NOT NULL,

These are INT UNSIGNED, but they are referencing primary keys that are INT.

The data types must be the same. Signed versus unsigned is enough to make them incompatible for purposes of foreign key references.

You might like this answer that I contributed to, which is a checklist of things that can go wrong with foreign keys: https://stackoverflow.com/a/4673775/20860

Can not add foreign key constraint when creating new tables

1 Answers1