mysql中的105异常_MySql错误：#105(代码150).当我创建数据库模式时,我收到错误代码150....

DROP SCHEMA IF EXISTS `YouthMinistry` ;

CREATE SCHEMA IF NOT EXISTS `YouthMinistry` DEFAULT CHARACTER SET utf16 COLLATE utf16_general_ci ;

USE `YouthMinistry` ;

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

-- Table `YouthMinistry`.`group`

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

DROP TABLE IF EXISTS `YouthMinistry`.`group` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`group` (

`groupid` INT NOT NULL AUTO_INCREMENT ,

`group_name` VARCHAR(100) NOT NULL ,

`group_desc` VARCHAR(255) NULL ,

PRIMARY KEY (`groupid`) )

ENGINE = InnoDB;

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

-- Table `YouthMinistry`.`groupmembers`

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

DROP TABLE IF EXISTS `YouthMinistry`.`groupmembers` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`groupmembers` (

`groupid` INT NOT NULL ,

`memberid` INT NOT NULL ,

PRIMARY KEY (`groupid`, `memberid`) ,

INDEX `groupid_idx` (`groupid` ASC) ,

CONSTRAINT `groupid`

FOREIGN KEY (`groupid` )

REFERENCES `YouthMinistry`.`group` (`groupid` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `YouthMinistry`.`role`

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

DROP TABLE IF EXISTS `YouthMinistry`.`role` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`role` (

`roleid` INT NOT NULL AUTO_INCREMENT ,

`role_name` VARCHAR(100) NOT NULL ,

`role_desc` VARCHAR(255) NULL ,

PRIMARY KEY (`roleid`) )

ENGINE = InnoDB;

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

-- Table `YouthMinistry`.`rolemembers`

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

DROP TABLE IF EXISTS `YouthMinistry`.`rolemembers` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`rolemembers` (

`roleid` INT NOT NULL ,

`memberid` INT NOT NULL ,

PRIMARY KEY (`roleid`, `memberid`) ,

INDEX `groupid_idx` (`roleid` ASC) ,

FOREIGN KEY (`roleid` )

REFERENCES `YouthMinistry`.`role` (`roleid` )

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `YouthMinistry`.`users`

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

DROP TABLE IF EXISTS `YouthMinistry`.`users` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`users` (

`memberid` INT NOT NULL AUTO_INCREMENT ,

`username` VARCHAR(45) NOT NULL ,

`password` VARCHAR(45) NOT NULL ,

`email` VARCHAR(100) NULL ,

`first_name` VARCHAR(45) NOT NULL ,

`last_name` VARCHAR(45) NOT NULL ,

`created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

`updated` DATETIME NOT NULL ,

PRIMARY KEY (`memberid`, `username`, `password`) ,

UNIQUE INDEX `username_UNIQUE` (`username` ASC) ,

INDEX `memberid_idx` (`memberid` ASC) ,

INDEX `memberid_idx1` (`memberid` ASC) ,

FOREIGN KEY (`memberid` )

REFERENCES `YouthMinistry`.`groupmembers` (`memberid` )

ON DELETE CASCADE

ON UPDATE CASCADE,

FOREIGN KEY (`memberid` )

REFERENCES `YouthMinistry`.`rolemembers` (`memberid` )

ON DELETE CASCADE

ON UPDATE CASCADE)

ENGINE = InnoDB;

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

-- Table `YouthMinistry`.`eventgroup`

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

DROP TABLE IF EXISTS `YouthMinistry`.`eventgroup` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`eventgroup` (

`eventid` INT NOT NULL ,

`groupid` VARCHAR(45) NOT NULL ,

PRIMARY KEY (`eventid`, `groupid`) ,

INDEX `groupid_idx` (`groupid` ASC) ,

FOREIGN KEY (`groupid`)

REFERENCES `YouthMinistry`.`group` (`groupid`)

ON DELETE NO ACTION

ON UPDATE NO ACTION)

ENGINE = InnoDB;

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

-- Table `YouthMinistry`.`event`

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

DROP TABLE IF EXISTS `YouthMinistry`.`event` ;

CREATE TABLE IF NOT EXISTS `YouthMinistry`.`event` (

`eventid` INT NOT NULL AUTO_INCREMENT ,

`event_name` VARCHAR(255) NOT NULL ,

`event_desc` VARCHAR(255) NULL ,

PRIMARY KEY (`eventid`) ,

INDEX `eventid_idx` (`eventid` ASC) ,

FOREIGN KEY (`eventid` )

REFERENCES `YouthMinistry`.`eventgroup` (`eventid` )

ON DELETE CASCADE

ON UPDATE CASCADE)

ENGINE = InnoDB;

我正在为我正在制作的网站创建数据库,当我运行创建脚本时,它会给我以下错误：

错误代码：1005.无法创建表’youthministry.users'(错误号：150)

我检查了主要和外键声明只是为了确保一切正确.

任何帮助深表感谢.此外,这仍然是一个原型,欢迎任何关于初始架构的评论.我还是数据库设计的新手.

最佳答案 您只能在一个引用另一个表上的键的表上创建外键.此特定问题是memberid不是groupmembers或rolemembers表上的键.只需将KEY(memberid)添加到这些表中即可.

我们的另一个问题是外键类型必须匹配. eventgroup具有groupid varchar,但引用的是具有groupid INT的groups表.纠正这个.

至于建议,我强烈建议每个主键只有一列：自动增量代理键.你也应该使这些无符号整数.