Failed to add the foreign key constraint in popsql

Question

Error: Failed to add the foreign key constraint. Missing column 'userid' for constraint 'buyer_ibfk_1' in the referenced table 'users'

Error: Failed to add the foreign key constraint. Missing column 'userid' for constraint 'seller_ibfk_1' in the referenced table 'users'

CREATE TABLE Users(
    user_id INT NOT NULL PRIMARY KEY,
    name VARCHAR(50),
    phoneNo VARCHAR(20)
);
DESCRIBE Users;

CREATE TABLE Buyer
(
    userid INT NOT NULL
    ,PRIMARY KEY(userid)
    ,FOREIGN KEY(userid) REFERENCES Users(userid)
);

CREATE TABLE Seller
(
    userid INT NOT NULL
    ,PRIMARY KEY(userid)
    ,FOREIGN KEY(userid) REFERENCES Users(userid)
);

Answer 1

The error message suggests users table has a column called userid, but the table DDL schema specify the column as user_id instead of userid (there is a '_' between user and Id). If that naming is intended, use the following query for foreign key creation:

CREATE TABLE Users( user_id INT NOT NULL PRIMARY KEY, name VARCHAR(50), phoneNo VARCHAR(20) ); 

CREATE TABLE Buyer ( userid INT NOT NULL ,PRIMARY KEY(userid) ,FOREIGN KEY(userid) REFERENCES Users(user_id) );

CREATE TABLE Seller ( userid INT NOT NULL ,PRIMARY KEY(userid) ,FOREIGN KEY(userid) REFERENCES Users(user_id) );

Verified the DDL build are successful in db fiddle