Search code examples
mysqlsqlwordpressforeign-keys

MySQL can't create foreign key reference with table wp_users

I want to create a table to save a clients details for a WordPress project. I want to reference that table to a WordPress user but it always gives me the following message:

Error: 150 "Foreign key constraint is incorrectly formed")

I googled a lot and found a few people having the same problem but their fixes didn't work in my case. I tried leaving ON DELETE and ON UPDATE out and renamed my column names but nothing changed the error message.

In my other tables creating foreign key references were not problem at all. Just wp_users doesn't want to be referenced this way.

CREATE TABLE IF NOT EXISTS klienten(
    klienten_id INTEGER PRIMARY KEY auto_increment,
    fk_user_id BIGINT(20),

    FOREIGN KEY(fk_user_id) REFERENCES wp_users(ID) ON DELETE RESTRICT ON UPDATE CASCADE,

    name VARCHAR(50),
    telefonnummer VARCHAR(15),
    email VARCHAR(60),
    logo_url VARCHAR(120)
);

Working in MySQL with the InnoDB Engine, WordPress Version 5.8 and executing my SQL script in phpmyadmin.

Does anyone have a clue whats wrong?

Solution

  • https://codex.wordpress.org/Database_Description#Table:_wp_users says that wp_users.id is of type BIGINT UNSIGNED.

    The fk_user_id column in your table is BIGINT. You have to make sure the data types are compatible to make a foreign key of one reference the other, and the difference between a signed integer type and an unsigned integer type is enough to spoil that.

    The (20) part is irrelevant, because that's just a display hint anyway. But the signed vs. unsigned is significant for this purpose.