errno: 150 & ldquo; the foreign key constraint is badly formed & rdquo; Nothing helps

advertisements

sorry for maybe stupid question, but I stack with my sql query. Tried out many methods to avid it but still have error 150. I've created 3 tables and one with foreighn keys:

Table users

create table users(
   id int(11) primary key auto_increment,
   unique_id varchar(23) not null unique,
   name varchar(50) not null,
   cname varchar(50) not null,
   email varchar(100) not null unique,
   encrypted_password varchar(80) not null,
   salt varchar(10) not null,
   created_at datetime,
   updated_at datetime null
);

Table behaviours

CREATE TABLE behaviours (
   id int(2) AUTO_INCREMENT PRIMARY KEY,
   bName varchar(100) NOT NULL
);

Table severytys

CREATE TABLE severitys (
   id int(2) AUTO_INCREMENT PRIMARY KEY,
   severity varchar(10) NOT NULL
);

And here it shows errors:

CREATE TABLE child_behaviours(
   id int(11) primary key auto_increment,
   name varchar(50) not null,
   cname varchar(50) not null,
   bName varchar(100) NOT NULL,
   severity varchar(10) NOT NULL,
   start_at datetime,
   stop_at datetime null,
   FOREIGN KEY (id) REFERENCES users(id),
   FOREIGN KEY (bName) REFERENCES behaviours(bName),
   FOREIGN KEY (severity) REFERENCES severitys(severity)
);

Will be really pleasent to have an answer to this issue


You can only create a FK on a column that is both UNIQUE and NOT NULL. This is usually, but not always, the primary key of the referenced table.

If you reference anything other than the PK, you need a really really good reason.

(I'd even add to that, that you need a really really good reason to use anything other than an INT or a couple INTs as a PK...)

Now, child_behaviours has several errors:

  • Duplication of columns already present in users. If you reference users by its id, there is no need to duplicate the columns.
  • Reference to behaviors(bName) instead of using its id
  • same thing for reference to severity

Also, there is the generic mistake of using "id" columns. Please call them "user_id", "severity_id", etc, and then call them the same in your references. This will make your life much easier, and avoid stuff like:

SELECT foo.id AD foo_id, bar.id AS bar_id FROM foo JOIN bar ON ...