MySQL-error 150 solution

Question

I have the following MySQL scripts:

CREATE TABLE user_roles (
  id INT AUTO_INCREMENT,
  PRIMARY KEY(id),
  name TEXT NOT NULL,
  access INT NOT NULL DEFAULT '0'
)
CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    password TEXT NOT NULL,
    date_created DATETIME,
    roles VARCHAR(50) NOT NULL,
    active INT DEFAULT '1',
    FOREIGN KEY(roles) REFERENCES user_roles(id)
)

It keeps giving me error 150. Maybe the database isn't well planned? Any help will be greatly appreciated.

Answer 1

The data types of your users.roles and user_roles.id columns must be the same for the FOREIGN KEY constraint to work correctly. Instead try making users.roles an INT:

CREATE TABLE users (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    name TEXT NOT NULL,
    email TEXT NOT NULL,
    password TEXT NOT NULL,
    date_created DATETIME,
    -- Change this...
    roles INT NOT NULL,
    active INT DEFAULT '1',
    FOREIGN KEY(roles) REFERENCES user_roles(id)
)

UPDATE According to comments, users.roles should be text like "admin, moderator, etc." For correct data normalization, user_roles.id should be keyed against and to get the text name of the role, JOIN them in queries.