外键问题：错误1005（HY000）：无法创建表（errno：150） [英] Foreign key issue:ERROR 1005 (HY000): Can't create table (errno: 150)

问题描述

ERROR 1005（HY000）：Can not create table（errno：150）;
我知道它有一些东西做外键，但我已经检查，看看他们是否有相同的信息类型等，我找不到问题。视频，导演，角色和成员创造; （

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $' $ b actorName VARCHAR（40）NOT NULL，
PRIMARY KEY（actorNo）
）ENGINE = InnoDB; （



$ b CREATE TABLE IF NOT EXISTS职员（
StaffNo VARCHAR（15）非NULL $ b $ name VARCHAR（40）NOT NULL $ b $ position VARCHAR 40），
薪水FLOAT（5,2），
branchNo VARCHAR（15）NOT NULL，
FOREIGN KEY（branchNo）REFERENCES分支（branchNo），
UNIQUE KEY ，branchNo）
）ENGINE = InnoDB;

SHOW TABLES;

CREATE TABLE IF NOT EXISTS分支（
branchNo VARCHAR（15）NOT NULL，
Street VARCHAR（40），
City VARCHAR（20），
状态VARCHAR（40），
zipCode VARCHAR（6）NOT NULL，
StaffNo VARCHAR（15）NOT NULL，
PRIMARY KEY（branchNo），
UNIQUE KEY（zipCode） ，FOREIGN KEY（StaffNo）REFERENCES staff（StaffNo）ON DELETE NO ACTION ON UPDATE
CASCADE）ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS director（
directorNo VARCHAR（15）NOT NULL，
directorName VARCHAR（40）NOT NULL，
PRIMARY KEY（directorNo）
）ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS member（
fName VARCHAR（20），
lName VARCHAR（20），
address VARCHAR（100），
memberNo VARCHAR（15）NOT NULL，
PRIMARY KEY（memberNo），
UNIQUE KEY`CCfnmLnmAddr`（fName，lName，address）
）ENGINE = InnoDB;

CREATE TABLE IF NOT EXSTS注册（
branchNo VARCHAR（15）NOT NULL，
memberNo VARCHAR（15）NOT NULL，
StaffNo VARCHAR（15）NOT NULL ，
dateJoined date NOT NULL，
PRIMARY KEY（branchNo，memberNo），
FOREIGN KEY（memberNo）REFERENCES member（memberNo）ON DELETE SET NULL ON UPDATE
CASCADE，FOREIGN KEY （StaffNo）REFERENCES staff（StaffNo）ON DELETE SET NULL ON UPDATE
CASCADE
）ENGINE = InnoDB;

CREATE TABLE if NOT EXISTS videoforrent（
videoNo INT（15）NOT NULL，
available CHAR（1）NOT NULL，
catalogNo VARCHAR（15），
branchNo VARCHAR（15）NOT NULL，
PRIMARY KEY（videoNo），
UNIQUE KEY CCAvailBrnchNoCatNo（available，branchNo，catalogNo），FOREIGN KEY（catalogNo）REFERENCES video（catalogNo）ON DELETE CASCADE ON UPDATE
CASCADE，FOREIGN KEY（branchNo）REFERENCES分支（branchNo）ON DELETE CASCADE ON UPDATE
CASCADE
）ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS video（
catalogNo VARCHAR（10），
title VARCHAR（100）NOT NULL，
category VARCHAR（30），
dailyRental FLOAT（4,2），
FLOAT（5,2），
directorNo VARCHAR（10）NOT NULL，
PRIMARY KEY（catalogNo），
UNIQUE KEY` CCTitleDrNo`（title，directorNo）
）ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS rentalagreement（
rentalNo VARCHAR（15）NOT NULL，
dateOut DATE NOT NULL，
dateReturn DATE，
memberNo VARCHAR 15）NOT NULL，
videoNo INT（15）NOT NULL，
PRIMARY KEY（rentalNo），
UNIQUE KEY`CCdtOutMembrNoVidNo`（dateOut，memberNo，videoNo），
FOREIGN KEY （videoNo）参考videoforrent（videoNo）ON DELETE SET NULL ON UPDATE
CASCADE，FOREIGN KEY（memberNo）REFERENCES member（memberNo）ON DELETE NO ACTION ON UPDATE
CASCADE）ENGINE = InnoDB;

CREATE TABLE IF NOT EXISTS角色（
actorNo VARCHAR（15）NOT NULL，
catalogNo INT（11）NOT NULL，
charachter VARCHAR（40），
PRIMARY KEY（actorNo，catalogNo）
）ENGINE = InnoDB;

解决方案

您有FK定义会尝试设置null非空列上的值。因此，FK声明是不可能的。

  CREATE TABLE IF NOT EXISTS注册（
 branchNo VARCHAR（15）NOT NULL， 
 memberNo VARCHAR（15）** NOT NULL **，
 StaffNo VARCHAR（15）** NOT NULL **，
 dateJoined date NOT NULL，
 PRIMARY KEY（branchNo， memberNo），
 FOREIGN KEY（memberNo）REFERENCES member（memberNo）ON DELETE ** SET NULL ** ON UPDATE 
 CASCADE，
 FOREIGN KEY（StaffNo）REFERENCES staff（StaffNo）ON DELETE * * SET NULL ** ON UPDATE 
 CASCADE 
）ENGINE = InnoDB; 
  

您可以设置DELETE CASCADE或允许这些字段为空。

您可能在其他表上有相同的错误。我刚刚停在第一个错误，我发现：）

I'm getting this error: ERROR 1005 (HY000): Can't create table (errno: 150); I know it has something to do with the foreign keys but I have checked to see if they have the same info types etc and I can't find the problem. The video, director actor, role and member create; the rest do not.

CREATE TABLE IF NOT EXISTS actor (
  actorNo VARCHAR(15) NOT NULL,
  actorName VARCHAR(40) NOT NULL,
  PRIMARY KEY (actorNo)
) ENGINE=InnoDB;


CREATE TABLE IF NOT EXISTS staff (
  StaffNo VARCHAR (15) NOT NULL,
  name VARCHAR(40) NOT NULL,
  position VARCHAR(40) ,
  salary FLOAT(5,2) ,
  branchNo VARCHAR(15) NOT NULL,
FOREIGN KEY (branchNo) REFERENCES branch(branchNo),
  UNIQUE KEY (name,branchNo)
) ENGINE=InnoDB;

SHOW TABLES;

CREATE TABLE IF NOT EXISTS branch (
  branchNo VARCHAR(15) NOT NULL,
  Street VARCHAR (40) ,
  City VARCHAR (20) ,
  State VARCHAR (40) ,
  zipCode VARCHAR (6) NOT NULL,
  StaffNo VARCHAR (15) NOT NULL,
  PRIMARY KEY (branchNo),
  UNIQUE KEY (zipCode), FOREIGN KEY (StaffNo) REFERENCES staff (StaffNo) ON DELETE NO ACTION ON UPDATE 
CASCADE) ENGINE=InnoDB; 

CREATE TABLE IF NOT EXISTS director (
  directorNo VARCHAR(15) NOT NULL,
  directorName VARCHAR(40) NOT NULL,
  PRIMARY KEY (directorNo)
) ENGINE=InnoDB;   

CREATE TABLE IF NOT EXISTS member (
  fName VARCHAR(20) ,
  lName VARCHAR(20) ,
  address VARCHAR (100) ,
  memberNo VARCHAR(15) NOT NULL,
  PRIMARY KEY (memberNo),
  UNIQUE KEY `CCfnmLnmAddr` (fName,lName,address)
) ENGINE=InnoDB;  

CREATE TABLE IF NOT EXISTS registration (
  branchNo VARCHAR (15) NOT NULL,
  memberNo VARCHAR (15) NOT NULL,
  StaffNo VARCHAR (15) NOT NULL,
  dateJoined date NOT NULL,
  PRIMARY KEY (branchNo, memberNo), 
FOREIGN KEY (memberNo) REFERENCES member (memberNo) ON DELETE SET NULL ON UPDATE 
CASCADE, FOREIGN KEY (StaffNo) REFERENCES staff (StaffNo) ON DELETE SET NULL ON UPDATE 
CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS videoforrent (
  videoNo INT(15) NOT NULL,
  available CHAR(1) NOT NULL,
  catalogNo VARCHAR (15) ,
  branchNo  VARCHAR(15) NOT NULL,
  PRIMARY KEY (videoNo),
  UNIQUE KEY CCAvailBrnchNoCatNo (available, branchNo,catalogNo), FOREIGN KEY (catalogNo) REFERENCES video (catalogNo) ON DELETE CASCADE ON UPDATE 
CASCADE, FOREIGN KEY (branchNo) REFERENCES branch (branchNo) ON DELETE CASCADE ON UPDATE 
CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS video (
  catalogNo VARCHAR(10) ,
  title VARCHAR(100) NOT NULL,
  category VARCHAR(30) ,
  dailyRental FLOAT(4,2) ,
  price FLOAT(5,2) ,
  directorNo VARCHAR(10) NOT NULL,
  PRIMARY KEY (catalogNo),
  UNIQUE KEY `CCTitleDrNo` (title,directorNo)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS rentalagreement (
  rentalNo VARCHAR(15) NOT NULL,
  dateOut DATE NOT NULL,
  dateReturn DATE ,
  memberNo VARCHAR(15) NOT NULL,
  videoNo INT(15) NOT NULL,
  PRIMARY KEY (rentalNo),
  UNIQUE KEY `CCdtOutMembrNoVidNo` (dateOut,memberNo,videoNo),
  FOREIGN KEY (videoNo) REFERENCES videoforrent (videoNo) ON DELETE SET NULL ON UPDATE 
CASCADE, FOREIGN KEY (memberNo) REFERENCES member (memberNo) ON DELETE NO ACTION ON UPDATE 
CASCADE) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS role (
    actorNo VARCHAR(15) NOT NULL,
  catalogNo INT(11) NOT NULL,
  charachter VARCHAR(40),
  PRIMARY KEY (actorNo, catalogNo)
) ENGINE=InnoDB;

解决方案

You have FK definitions that will try to set a null value on non null columns. Therefore that FK declaration is impossible.

CREATE TABLE IF NOT EXISTS registration (
  branchNo VARCHAR (15) NOT NULL,
  memberNo VARCHAR (15) **NOT NULL**,
  StaffNo VARCHAR (15) **NOT NULL**,
  dateJoined date NOT NULL,
  PRIMARY KEY (branchNo, memberNo), 
FOREIGN KEY (memberNo) REFERENCES member (memberNo) ON DELETE **SET NULL** ON UPDATE 
CASCADE, 
FOREIGN KEY (StaffNo) REFERENCES staff (StaffNo) ON DELETE **SET NULL** ON UPDATE 
CASCADE
) ENGINE=InnoDB;

you can either set "on DELETE CASCADE" or allow those fields to be null.

You might have the same error on other tables. I just stopped at the first error I found :)

这篇关于外键问题：错误1005（HY000）：无法创建表（errno：150）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！