MySql错误：＃105（代码150）。当我创建我的数据库模式时，我收到一个150的错误代码。 [英] MySql Error: #105 (Code 150). When I create my database schema I receive an error code of 150.

问题描述

 如果出现青年事业，DROP SCHEMA 
 CREATE SCHEMA IF NOT EXISTS`YouthMinistry` DEFAULT CHARACTER SET utf16 COLLATE utf16_general_ci; 
使用`YouthMinistry`; 
 
  -  ---------------------------------------- ------------- 
  - 表`YouthMinistry`.`group` 
  -  ----------------- ------------------------------------ 
 DROP TABLE如果出现`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; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`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`）
参考`YouthMinistry `.`group`（`groupid`）
 ON DELETE NO ACTION 
更新没有动作
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`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; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`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`）
参考`YouthMinistry`.`role`（` `）
 ON DELETE NO ACTION 
更新无操作
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`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`）
参考`YouthMinistry```groupmembers`（`memberid`）
 ON DELETE CASCADE 
更新CASCADE，
 FOREIGN KEY（`memberid`）
参考`YouthMinistry```rolemembers`（`memberid`）
 ON DELETE CASCADE 
 ON UPDATE CASCADE）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`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`）
参考`YouthMinistry`.`group` （`groupid`）
 ON DELETE NO ACTION 
更新无操作）
 ENGINE = InnoDB; 
 
 
  -  ------------------------------------ ----------------- 
  - 表`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`）
参考`YouthMinistry`.`eventgroup`（`eventid`）
 ON DELETE CASCADE 
 ON UPDATE CASCADE 
 ENGINE = InnoDB; 
  

我正在为我正在制作的网站创建一个数据库，当我运行创建脚本，它给我以下错误：
错误代码：1005.无法创建表'youthministry.users'（errno：150）

我们看到以下来源可能解决了这个错误：
http://www.webdeveloper.com/forum/showthread.php?68260-mySQL-multiple-foreign-keys
http://forums.devarticles.com/mysql-development-50/mysql-foreign- key-problem-errno-150t-7704.html

我检查了主键和外键的声明，以确保一切正确。 p>

任何帮助都非常感激。此外，这仍然是一个原型，对初始架构的任何意见都是受欢迎的。我仍然是新的数据库设计。

解决方案

只能在一个表上引用另一个键上的外键表。这个具体问题是 memberid 不是 groupmembers 或 rolemembers 表。只需将 KEY（memberid）添加到这些表中，那么你将会很好。

另一个问题我们外键类型必须匹配。 事件组具有 groupid varchar ，但引用组表，其中有 groupid INT 。正确的。

---

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

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;

I'm working on creating a database for a website that I'm making and when I run the create script it gives me the following error: Error Code: 1005. Can't create table 'youthministry.users' (errno: 150)

I've looked at the following sources for possible solutions to this error: http://www.webdeveloper.com/forum/showthread.php?68260-mySQL-multiple-foreign-keys http://forums.devarticles.com/mysql-development-50/mysql-foreign-key-problem-errno-150t-7704.html

I've checked the primary and foreign key declarations just to make sure everything is correct.

Any help is much appreciated. Also this is still a prototype and any comments on the initial schema is welcome. I'm still new at database design.

解决方案

You can only create a foreign key on one table that references a key on another table. This specific problem is that memberid is not a key on either groupmembers or rolemembers tables. Simply add KEY (memberid) to those tables and you'll be good to go.

Another issue us that foreign key types must match. eventgroup has groupid varchar, but is referencing the groups table, which has groupid INT. Correct this.

---

As for suggestions, I very strongly recommend that each primary key be only one column: your auto-increment surrogate key. You should make these unsigned integers too.

这篇关于MySql错误：＃105（代码150）。当我创建我的数据库模式时，我收到一个150的错误代码。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！