Error in MySQL on create statement

Question

I have a doubt what I'm doing wrong with the following table statement:

MySQL returns error 150

DROP table usuario;
DROP table MiTabla;

CREATE TABLE usuario(
id smallint unsigned auto_increment primary key,
name varchar(20) not null
)ENGINE=InnoDB;

Insert into usuario (NAME) VALUES ('Antonio'),('Jose'),('Manuel');


CREATE TABLE MiTabla(
id smallint unsigned auto_increment primary key,
name varchar(20) not null,
foreign key (name) REFERENCES usuario (name)
) ENGINE = InnoDB;

Did you do a search on Stackoverflow? There seem to be several posts about error 150. — jcoppens, Jun 28 '15 at 02:11

score 0 · Accepted Answer · answered Jun 28 '15 at 01:58

0

You cannot add a foreign key relationship to just any column. It needs to have an index. The easiest method is:

CREATE TABLE usuario (
  id smallint unsigned auto_increment primary key,
  name varchar(20) not null unique
);

However, the correct method is to use primary keys for the relationship:

CREATE TABLE MiTabla(
  id smallint unsigned auto_increment primary key,
  usuario_id smalling unsigned not null,
  foreign key (usuario_id) REFERENCES usuario(id)
);

answered Jun 28 '15 at 01:58

Gordon Linoff

1,242,037
58
646
786

so how can i check if have a vlue in column of another table do i add an index? and if is how it should be done? – user2862454 Jun 28 '15 at 02:09
1

@user2862454 . . . Use the primary key. That is the right way to set up a foreign key relationship. – Gordon Linoff Jun 28 '15 at 02:14

Error in MySQL on create statement

1 Answers1