Innodb不接受外键

问题描述

我们无法运行下面的查询。我们有另一个名为person的表，它有一个主键person_id。正如你所看到的，我们试图把这个列作为我们的客户表的外键。 phpMyAdmin返回＃1064语法错误。

  CREATE TABLE如果不存在`resort`.`customers`（
`person_id` VARCHAR （45）NOT NULL，
`cid` INT UNSIGNED NOT NULL AUTO_INCREMENT，
 UNIQUE INDEX`person_id_UNIQUE`（`cid` ASC），
 PRIMARY KEY（`person_id`），
 UNIQUE INDEX`person_id_UNIQUE`（`person_id` ASC），
 CONSTRAINT`person_id` 
 FOREIGN KEY（）
 REFERENCES`resort`.`person`（）
 ON DELETE CASCADE 
 ON UPDATE CASCADE）
 ENGINE = InnoDB 
  

顺便提一句，是由Oracle的MySQL Workbench生成的。

解决方案

你需要在约束定义中包含列：

  CONSTRAINT person_id 
 FOREIGN KEY（）
 REFERENCES resort.person（）
 ON DELETE CASCADE 
 ON UPDATE CASCADE 
  

应该是：

  CONSTRAINT fk_person_id 
 FOREIGN KEY （person_id）
 REFERENCES resort.person（id）
 ON DELETE CASCADE 
 ON UPDATE CASCADE 
  

We cannot run the query below. We have another table named "person" and it has a primary key person_id. As you can see, we are trying to get this column as our (customers table's) foreign key. phpMyAdmin returns #1064 syntax error. What's going wrong here?

CREATE  TABLE IF NOT EXISTS `resort`.`customers` (
  `person_id` VARCHAR(45) NOT NULL ,
  `cid` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  UNIQUE INDEX `person_id_UNIQUE` (`cid` ASC) ,
  PRIMARY KEY (`person_id`) ,
  UNIQUE INDEX `person_id_UNIQUE` (`person_id` ASC) ,
  CONSTRAINT `person_id`
    FOREIGN KEY ()
    REFERENCES `resort`.`person` ()
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB

By the way, this query is generated by Oracle's MySQL Workbench.

解决方案

You need to include the columns in the constraint definition:

CONSTRAINT person_id
  FOREIGN KEY ()
  REFERENCES resort.person()
  ON DELETE CASCADE
  ON UPDATE CASCADE

should be:

CONSTRAINT fk_person_id
  FOREIGN KEY (person_id)
  REFERENCES resort.person (id)
  ON DELETE CASCADE
  ON UPDATE CASCADE

这篇关于Innodb不接受外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！