mysql - mysql工作台中的外键

Question

我是 mysql 初学者，有一个简单的问题。我的模式(书店)中有三个表。我想在他们之间建立关系。我的表:书籍(book_id、book_name、book_entity)、客户(customer_id、customer_name)、订单(book_id、customer_id、数量)。我在订单表中设置外键如下:

外键名称(book_id 和 customer_id)，引用表( '书店'，'书籍' '书店','顾客')

起初我填写了 books 表，但是当我试图在 customer 表中插入数据时出现以下错误:

  ERROR 1452: Cannot add or update a child row: a foreign key constraint fails (`bookshop`.`customer`, CONSTRAINT `customer_id` FOREIGN KEY (`customer_id`) REFERENCES `order` (`customer_id`) ON DELETE NO ACTION ON UPDATE NO ACTION)

SQL Statement:

INSERT INTO `bookshop`.`customer` (`customer_id`, `customer_name`, `customer_address`) VALUES (2, 'helen', 'newyork')

我该怎么办？

我的代码:

---

-- Table `bookshop`.`books`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `bookshop`.`books` (

  `book_id` INT NOT NULL ,

  `book_name` VARCHAR(45) NOT NULL ,

  `book_price` DECIMAL NOT NULL ,

  `book_entity` INT NOT NULL ,

  PRIMARY KEY (`book_id`) )

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `bookshop`.`customer`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `bookshop`.`customer` (

  `customer_id` INT NOT NULL ,

  `customer_name` VARCHAR(45) NOT NULL ,

  `customer_address` VARCHAR(45) NOT NULL ,

  PRIMARY KEY (`customer_id`) )

ENGINE = InnoDB;





-- -----------------------------------------------------

-- Table `bookshop`.`orders`

-- -----------------------------------------------------

CREATE  TABLE IF NOT EXISTS `bookshop`.`orders` (

  `book_id` INT NOT NULL ,

  `customer_id` INT NOT NULL ,

  `quantity` INT NOT NULL ,

  INDEX `book_id` (`book_id` ASC) ,

  INDEX `customer_id` (`customer_id` ASC) ,

  CONSTRAINT `book_id`

    FOREIGN KEY (`book_id` )

    REFERENCES `bookshop`.`books` (`book_id` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION,

  CONSTRAINT `customer_id`

    FOREIGN KEY (`customer_id` )

    REFERENCES `bookshop`.`customer` (`customer_id` )

    ON DELETE RESTRICT

    ON UPDATE RESTRICT)

ENGINE = InnoDB;

Answer 1

您的外键语句似乎有问题，请提供您用来创建外键的语句。

但是，您似乎创建了一个外键，它只允许您在存在给客户的订单时插入该客户。您可能想反过来做。