MYSQL-无法创建外部密钥 [英] MYSQL - Impossible to create an external key

问题描述

我是一个自学成才的CS，而且我真的是mySQL的新手.我创建了一个名为"jobs"的表.我想创建一个具有3列的新表keywords:

I am a self taught CS and I am really novice at mySQL. I created a table called "jobs". I would like to create a new table keywords with 3 columns:

• keyword_id作为主键
• job_id作为jobs表中的外键
• keyword，文本

• keyword_id as a primary key
• job_id as a foreign key from the jobs table
• keyword, text

这是我写的查询:

   CREATE TABLE `keywords` (
  `keyword_id` int(11) NOT NULL AUTO_INCREMENT,
   PRIMARY KEY(`keyword_id`),
  `keyword` text NOT NULL,
  FOREIGN KEY (job_id) REFERENCES jobs(job_id)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

我收到此错误消息:

Key column 'job_id' doesn't exist in table

当前作业表代码如下:

    CREATE TABLE `jobs` (
      `title` text NOT NULL,
      `type` text NOT NULL,
      `location` text NOT NULL,
      `salary` int(11) NOT NULL,
      `description` text NOT NULL,
      `date` date NOT NULL,
      `job_id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`job_id`)

) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

推荐答案

您需要在keywords表中具有一列以容纳外键.

You need to have a column in the keywords table to hold the foreign key.

赞

CREATE TABLE `jobs` (
      `title` text NOT NULL,
      `type` text NOT NULL,
      `location` text NOT NULL,
      `salary` int(11) NOT NULL,
      `description` text NOT NULL,
      `date` date NOT NULL,
      `job_id` int(11) NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

CREATE TABLE `keywords` (
    `keyword_id` int(11) NOT NULL AUTO_INCREMENT,
    `keyword` text NOT NULL,
    `job_id` int(11) NOT NULL,              #<- new column
    PRIMARY KEY(`keyword_id`),
    FOREIGN KEY (job_id) REFERENCES jobs(job_id)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=latin1;

这篇关于MYSQL-无法创建外部密钥的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！