1072 “user_id does not exist in table.” any way to fix?

by

So I was creating a database for the first time but my user_id does not exist? How can I fix this? My code was:

CREATE TABLE comments (
    id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(30),
    comment_text TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIME,
    users_id INT(11) NOT NULL,
    PRIMARY KEY (user_id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL
);

Create a comment table but user_id does not exist.

  • 1

    Is it a typo? You are creating users_id and then primary and foreign key is user_id.

    – 

  • 1

    Also why is the primary key user_id and not id?

    – 

  • you should also know that a column that is defined as "auto-increment" must be defined as a key

    – 

There are few issues

  • PRIMARY KEY should be id to refer comment id
  • Use CURRENT_TIMESTAMP instead of CURRENT_TIME to keep data with time
  • users_id cannot be NOT NULL in creating relation
  • possible typo users_id instead of user_id in relation many to one
  • you probably mean CASCADE instead of SET NULL on relation in this case if user will be delated from users table it will delete all comments from this user
CREATE TABLE comments (
    id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(30),
    comment_text TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    user_id INT(11),
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE
);

Leave a Comment