表1可以建立:-- ------------------------------------------------------- Table `mydb`.`stu_info`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `mydb`.`stu_info` ( `stu_id` INT NOT NULL , `stu_name` VARCHAR(10) NOT NULL , `class_id` INT NOT NULL , `sex` INT NOT NULL , `brithday` DATETIME NOT NULL , `brithplace` VARCHAR(45) NULL , `department` VARCHAR(45) NOT NULL , `political` VARCHAR(45) NOT NULL , PRIMARY KEY (`stu_id`, `class_id`, `stu_name`) , INDEX `class_id` (`class_id` ASC) , CONSTRAINT `class_id` FOREIGN KEY (`class_id` ) REFERENCES `mydb`.`class_info` (`class_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;表2可以建立:-- ------------------------------------------------------- Table `mydb`.`stu_contact`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `mydb`.`stu_contact` ( `stu_id` INT NOT NULL , `qq` VARCHAR(15) NULL , `email` VARCHAR(25) NULL , `mobile` VARCHAR(12) NOT NULL , `telephone` VARCHAR(12) NULL , `address` VARCHAR(45) NULL , `postcode` VARCHAR(8) NULL , PRIMARY KEY (`stu_id`) , INDEX `stu_id` (`stu_id` ASC) , CONSTRAINT `stu_id` FOREIGN KEY (`stu_id` ) REFERENCES `mydb`.`stu_info` (`stu_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;
表3的结构和表2一样,但是就是建立不了,那个constraint也没有重名,外键和参照的列的类型也是一样的,请大虾们指导啊..-- ------------------------------------------------------- Table `mydb`.`user_info`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `mydb`.`user_info` ( `stu_name` VARCHAR(10) NOT NULL , `user_pwd` VARCHAR(10) NOT NULL , `user_power` INT NOT NULL , PRIMARY KEY (`stu_name`) , INDEX `stu_name` (`stu_name` ASC) , CONSTRAINT `stu_name` FOREIGN KEY (`stu_name` ) REFERENCES `mydb`.`stu_info` (`stu_name` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;
报错:ERROR 1005 (HY000): Can't create table '.xxxxx.frm' (errno: 150) 为什么呢?
表3的结构和表2一样,但是就是建立不了,那个constraint也没有重名,外键和参照的列的类型也是一样的,请大虾们指导啊..-- ------------------------------------------------------- Table `mydb`.`user_info`-- -----------------------------------------------------CREATE TABLE IF NOT EXISTS `mydb`.`user_info` ( `stu_name` VARCHAR(10) NOT NULL , `user_pwd` VARCHAR(10) NOT NULL , `user_power` INT NOT NULL , PRIMARY KEY (`stu_name`) , INDEX `stu_name` (`stu_name` ASC) , CONSTRAINT `stu_name` FOREIGN KEY (`stu_name` ) REFERENCES `mydb`.`stu_info` (`stu_name` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;
报错:ERROR 1005 (HY000): Can't create table '.xxxxx.frm' (errno: 150) 为什么呢?
------------------------
100510 4:34:39 Error in foreign key constraint of table mydb/user_info: FOREIGN KEY (`stu_name` )
REFERENCES `mydb`.`stu_info` (`stu_name` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB:
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html
for correct foreign key definition.
发现原来被参照的要是被参照表的第一列?referenced columns appear as the first columns
???....还是怎么样?
另外表的设计上一般不用stu_name做为外键来参照,因为stu_name有可能重名,你的
user_info表一般情况下通常设计为
CREATE TABLE IF NOT EXISTS `user_info` (
`stu_id` INT NOT NULL ,
`stu_name` VARCHAR(10) NOT NULL ,
`user_pwd` VARCHAR(10) NOT NULL ,
`user_power` INT NOT NULL ,
PRIMARY KEY (`stu_id`) ,
CONSTRAINT `stu_id11`
FOREIGN KEY (`stu_id` )
REFERENCES `stu_info` (`stu_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
INDEX `stu_name`(`stu_name`), CONSTRAINT `class_id` FOREIGN KEY (`class_id` ) REFERENCES `mydb`.`class_info` (`class_id` ) ON DELETE NO ACTION ON UPDATE NO ACTION)ENGINE = InnoDB;给你的`stu_name` 字段加唯一索引试试看
还有feixianxxx也是很好的建议.受教了.
谢谢啦...