MySQL: errno: 150 on foregin key asignment

Question

I'm trying to add a foreign key to a table and I get the following error:

ERROR 1005 (HY000): Can't create table 'tablename.#sql-5cf_179' (errno: 150)

I use this query:

alter table table_1 add foreign key (col_1) references table_2 (col_2);

Both columns have the same datatype (varchar(50)), same collation, they're not null and I changed the default value to something different from null too.

I checked SHOW ENGINE INNODB STATUS and got this:

Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with

= InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition.

I have no idea what do to next...

EDIT more info added:

    |snmptt_nodes | CREATE TABLE `snmptt_nodes` (
      `ID` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `ip` varchar(50) NOT NULL DEFAULT 'no_ip_found',
      `last_response` datetime NOT NULL,
      `state` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`ID`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 

| snmptt | CREATE TABLE `snmptt` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `eventname` varchar(4000) DEFAULT NULL,
  `eventid` varchar(4000) DEFAULT NULL,
  `trapoid` varchar(4000) DEFAULT NULL,
  `enterprise` varchar(4000) DEFAULT NULL,
  `community` varchar(4000) DEFAULT NULL,
  `hostname` varchar(4000) DEFAULT NULL,
  `agentip` varchar(50) NOT NULL DEFAULT 'no_ip_found',
  `category` varchar(4000) DEFAULT NULL,
  `severity` varchar(4000) DEFAULT NULL,
  `uptime` varchar(4000) DEFAULT NULL,
  `traptime` varchar(4000) DEFAULT NULL,
  `formatline` varchar(4000) DEFAULT NULL,
  `timestamp` datetime DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=206352 DEFAULT CHARSET=latin1 |

| snmptt_listpolls | CREATE TABLE `snmptt_listpolls` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ip` varchar(50) NOT NULL DEFAULT 'no_ip_found',
  `polling_time` int(10) NOT NULL,
  `communitydata` varchar(100) NOT NULL,
  `snmp_oid` varchar(250) DEFAULT NULL,
  `lastcheck` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=55 DEFAULT CHARSET=latin1 |

Can you show the CREATE TABLE for the two tables in question. — Dan, Oct 13 '15 at 16:03
Oh, I meant `SHOW CREATE TABLE`. That will allow you to see the structure of your tables. https://dev.mysql.com/doc/refman/5.0/en/show-create-table.html — Dan, Oct 13 '15 at 16:17
Those are the three tables I want to use. I want to have a foreign key for the table snmptt_nodes to the other two. The foreign key should be the ip. — Bobby Cheny Chen, Oct 13 '15 at 16:23
Please edit to show your actual `ALTER TABLE` statement so we can see which columns are truly being referenced. The error indicates that you are attempting to reference a column without an index. For a FK constraint to succeed, the referenced column _must_ have an index (or compound index works in some circumstances). Most often, it's a primary key and the requirement is fulfilled. Yours sounds like it is not referencing a PK though, so another index is needed. — Michael Berkowski, Oct 13 '15 at 16:29
Ok, that might be you problem. A column can only foreign key to on column. That is: `snmptt_nodes.ip` cannot foreign key to bother `snmptt.agentip` and `snmptt_listpolls.ip`. Also "MySQL requires indexes on foreign keys and referenced keys". Read over this: http://dev.mysql.com/doc/refman/5.6/en/create-table-foreign-keys.html — Dan, Oct 13 '15 at 16:29
One of them is: alter table snmptt add foreign key (agentip) references snmptt_nodes (ip); — Bobby Cheny Chen, Oct 13 '15 at 16:29
What I'm really trying to do is create a trigger that when data is inserted into the listpolls compares timestamps and depending on the output it will insert a different name into another column, so I think the table should be linked by foreign keys. — Bobby Cheny Chen, Oct 13 '15 at 16:33
That means you must first add an index to `snmptt_nodes.ip`. If it is a unique value, then consider just making that the primary key instead of including the auto-incrementing `ID`. — Michael Berkowski, Oct 13 '15 at 16:33
Well, yeah, I think it could be a unique value, I will check that first. — Bobby Cheny Chen, Oct 13 '15 at 16:34
Starting with `ALTER TABLE snmptt_nodes ADD UNIQUE INDEX (ip);` should work for the FK definition you listed. — Michael Berkowski, Oct 13 '15 at 16:37
And in fact it does: http://sqlfiddle.com/#!9/8009a All tables are created, the index is added, then the FK constraint is successfully created. — Michael Berkowski, Oct 13 '15 at 16:41

MySQL: errno: 150 on foregin key asignment

0 Answers0