Error number 150 in the Foreign Key

Question

I intend to create a table with two columns (bus_id, bus_passengers). The bus_id is going to be the primary key and will be the foreign key from another created table, which is called "beacap_locationLog", column node_id.

This is the code I wrote (mySQL):

CREATE TABLE Bus(
   bus_id INT (10) UNSIGNED NOT NULL,
   bus_passengers INT,
   PRIMARY KEY (bus_id),
   FOREIGN KEY (bus_id) REFERENCES beacap_locationLog(node_id)
);

It's giving me this error:

#1005 - Can't create table 'pei.Bus' (errno: 150)

I don't know what the problem is.

Post the structure of `beacap_locationLog`. Err 150 is due usually to a type mismatch between the column and the one it references. They must match exactly. It doesn't often make sense for a column to be both a primary key and a foreign key though... — Michael Berkowski, Mar 11 '14 at 17:46
Take a look at this [link][1] - the problem is really similar. [1]: http://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150 — user3240544, Mar 11 '14 at 17:47

Up_One · Answer 1 · 2014-03-11T19:26:26.373

Cannot have the primary + foreign key on the same ! column with the same name

Shoul be like this !
-see i have added a new column !!

 CREATE TABLE Bus(
       bus_id INT (10) UNSIGNED NOT NULL,
       bus_passengers INT,
       node_id_fk int,
       PRIMARY KEY (bus_id),
       FOREIGN KEY (node_id_fk) REFERENCES beacap_locationLog(node_id)
    );

also the node_id_fk and node_id must have the same type !

Ok , see full example :

mysql> create table beacap_locationLog(
     -> node_id int
    -> );

Query OK, 0 rows affected (0.26 sec)

mysql> CREATE TABLE Bus(
    ->        bus_id INT (10) UNSIGNED NOT NULL,
    ->        bus_passengers INT,
    ->        node_id_fk int,
    ->        PRIMARY KEY (bus_id),
    ->        FOREIGN KEY (node_id_fk) REFERENCES beacap_locationLog(node_id)
    ->     );
Query OK, 0 rows affected (0.03 sec)

mysql> desc beacap_locationLog;
+---------+---------+------+-----+---------+-------+
| Field   | Type    | Null | Key | Default | Extra |
+---------+---------+------+-----+---------+-------+
| node_id | int(11) | YES  |     | NULL    |       |
+---------+---------+------+-----+---------+-------+
1 row in set (0.00 sec)

mysql> desc Bus;
+----------------+------------------+------+-----+---------+-------+
| Field          | Type             | Null | Key | Default | Extra |
+----------------+------------------+------+-----+---------+-------+
| bus_id         | int(10) unsigned | NO   | PRI | NULL    |       |
| bus_passengers | int(11)          | YES  |     | NULL    |       |
| node_id_fk     | int(11)          | YES  | MUL | NULL    |       |
+----------------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql>

TBL Bus definition

mysql> show create table Bus\G
           Table: Bus
    Create Table: CREATE TABLE Bus (
      bus_id int(10) unsigned NOT NULL,
      bus_passengers int(11) default NULL,
      node_id_fk int(11) default NULL,
      PRIMARY KEY  (bus_id),
      KEY node_id_fk (node_id_fk)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1
    1 row in set (0.01 sec)

Error number 150 in the Foreign Key

1 Answers1