MySQL ERROR 1005, Can't create table errno 150

Question

I'm trying to connect "articles > a_keywords" to "art_keys > art_id"

articles table:

| articles | CREATE TABLE `articles` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(50) DEFAULT NULL,
  `source` varchar(255) DEFAULT NULL,
  `press` varchar(60) DEFAULT NULL,
  `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `a_keywords` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 |

art_keys table:

| art_keys | CREATE TABLE `art_keys` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `art_id` int(10) unsigned DEFAULT NULL,
  `keyw_id` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `keyw_id` (`keyw_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Foreign Key command:

ALTER TABLE articles ADD FOREIGN KEY (a_keywords) REFERENCES art_keys(art_id);

I have successfully connected "keywords > k_articles" and "art_keys > keyw_id" but i don't know why it doesn't work with "articles > a_keywords".

I tried making a new table with id and a_keywords column but still didn't work. I believe i fixed all the causes listed in : http://www.eliacom.com/mysql-gui-wp-errno-150.php Help me!

| keywords | CREATE TABLE `keywords` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `words` varchar(100) DEFAULT NULL,
  `k_articles` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`),
  KEY `k_articles` (`k_articles`),
  CONSTRAINT `keywords_ibfk_1` FOREIGN KEY (`k_articles`) REFERENCES `art_keys` (`keyw_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1 |

Possible duplicate of [MySQL Creating tables with Foreign Keys giving errno: 150](https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150) — Ken White, Nov 29 '18 at 01:04
Hi. There is no referenceable PK/UNIQUE (art_id) in art_keys. (Obviously,) This is a faq. Please always google error messages & many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & 'site:stackoverflow.com' & tags & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. — philipxy, Nov 29 '18 at 01:10

score 0 · Answer 1 · answered Nov 29 '18 at 08:33

Just in case someone might run into the same problem i faced, Due to short ignorance, i'm leaving the solution i found. Column you are trying to reference(parent table's column) needs to be indexed. I thought there needs to be one indexed column...

in my example, art_id in art_keys table needed to be indexed by using:

alter table add index art_id(art_id)

before referenced in foreign key declaration.

MySQL ERROR 1005, Can't create table errno 150

1 Answers1