mysql - 在删除时在mysql中设置空创建表

Question

我要创建一个名为 patientmedicinecategory 的表。如下所示:-

 CREATE TABLE patientmedicinecategory
 (
 pmc         int(11) NOT NULL AUTO_INCREMENT,
 patient_id  int(11) NOT NULL,
 med_id      int(3) NOT NULL,
 cat_id      int(3) NOT NULL,
 testname    varchar(100) NOT NULL,

 PRIMARY KEY(pmc),
 UNIQUE KEY(patient_id, med_id,cat_id,testname),
 FOREIGN KEY(patient_id) REFERENCES patient(patient_id) ON UPDATE CASCADE ON DELETE    
 CASCADE,
 FOREIGN KEY(med_id) REFERENCES medicine(med_id) ON UPDATE CASCADE ON DELETE CASCADE,
 FOREIGN KEY(cat_id) REFERENCES category(cat_id) ON UPDATE CASCADE ON DELETE SET NULL
 )ENGINE=InnoDB;

我的病人表是:-

  CREATE TABLE patient
  (
  patient_id    int NOT NULL AUTO_INCREMENT,
  salutation    ENUM('Mr.','Mrs.','Miss.','Dr.') NOT NULL,
  name      varchar(50) NOT NULL,
  email_id  varchar(100),
  year      int(4) NOT NULL,
  month     int(3),
  day       int(3),
  sex       ENUM('M','F') NOT NULL,
  contactno         varchar(50),    

  PRIMARY KEY(patient_id)
  )ENGINE=InnoDb;

药 table 是:-

  CREATE TABLE medicine
  (
  med_id  int(3) NOT NULL,
  name    varchar(40) NOT NULL,

  PRIMARY KEY (med_id)
  )ENGINE=InnoDB;

类别表是:-

  CREATE TABLE category
  (
  cat_id    int(3) NOT NULL,
  name      varchar(20) NOT NULL,

  PRIMARY KEY (cat_id)
  )ENGINE=InnoDB;

但是当我尝试创建它时出现错误:-

   ERROR 1005 (HY000): Can't create table 'nutech3.patientmedicinecategory' (errno: 150)

我尝试了很多，但都没有成功。请帮我 。提前致谢

Answer 1

如果您以 root 身份登录并运行 SHOW ENGINE INNODB STATUS，您将看到确切的错误消息是这样的:

------------------------
LATEST FOREIGN KEY ERROR
------------------------
130405 11:55:42 Error in foreign key constraint of table test/patientmedicinecategory:
FOREIGN KEY(cat_id) REFERENCES category(cat_id) ON UPDATE CASCADE ON DELETE SET NULL
 )ENGINE=InnoDB:
You have defined a SET NULL condition though some of the
columns are defined as NOT NULL.

这是有问题的行:

FOREIGN KEY(cat_id) REFERENCES category(cat_id) ON UPDATE CASCADE ON DELETE SET NULL

MySQL 无法使 patientmedicinecategory.cat_id 为 NULL，因为它是这样定义的:

cat_id      int(3) NOT NULL,

您的选择是:

• 允许cat_id为NULL
• 设置不同的ON DELETE条件