无法在mysql-workbench中添加外键约束

Question

以下sql脚本导致错误代码: 1215。无法在MySQL工作台中添加外键约束

   DROP TABLE IF EXISTS `bookings`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `van_id` int(11) NOT NULL,
  `driver_id` int(11) NOT NULL,
  `route_id` int(11) NOT NULL,
  `registered_seats` int(11) NOT NULL,
  `departure_time` varchar(11) NOT NULL,
  `arival_time` varchar(11) NOT NULL,
  `departure_date` varchar(11) NOT NULL,
  `total_cost` int(11) NOT NULL,
  `expected_price` int(11) NOT NULL,


  PRIMARY KEY (`id`),
  FOREIGN KEY (`van_id`) REFERENCES `van` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`route_id`) REFERENCES `route` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

作为参考，下面是相关表的脚本，所有这些都很好。

为了桌子货车，

DROP TABLE IF EXISTS `van`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `van` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `vehicle_registration` varchar(45) NOT NULL UNIQUE,
  `vehicle_type` varchar(45) NOT NULL,
  `total_seats` int(11) NOT NULL,
  `category` varchar(45) NOT NULL,
  `flagged` int(2) DEFAULT 0 NOT NULL,


  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

对于餐桌司机来说，

DROP TABLE IF EXISTS `driver`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `driver` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(45) NOT NULL ,
  `last_name` varchar(45) DEFAULT NULL,
  `cnic` varchar(45) NOT NULL UNIQUE,
  `license_number` varchar(11) NOT NULL UNIQUE,
  `phonenumber` int(11) NOT NULL unique,
  `picture` varchar(15) NOT NULL unique, 
  `flagged` int(2) DEFAULT 0,


  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

关于餐桌路线，

DROP TABLE IF EXISTS `route`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `route` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source` varchar(45) NOT NULL,
  `destination` varchar(45) NOT NULL,
  `exp_t_time` varchar(45) NOT NULL,
  `eco_fare` int(11) NOT NULL,
  `ac_fare` int(11) NOT NULL,
  `state` varchar(45) NOT NULL,


  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

Answer 1

我试图设置NULL一个属性(在外键约束中)，这个属性应该是NULL。我在预订mysql脚本中做了以下更改。

在此之前：

  FOREIGN KEY (`van_id`) REFERENCES `van` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE SET NULL,
  FOREIGN KEY (`route_id`) REFERENCES `route` (`id`) ON DELETE SET NULL

之后：

  FOREIGN KEY (`van_id`) REFERENCES `van` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`driver_id`) REFERENCES `driver` (`id`) ON DELETE CASCADE,
  FOREIGN KEY (`route_id`) REFERENCES `route` (`id`) ON DELETE CASCADE