外键约束错误 - Error foreign key constraint

我正在尝试调试一个简单的SQL数据库。总是出现

的错误

SQL query:

CREATE TABLE Groups (
  groupId            int(11) NOT NULL AUTO_INCREMENT,
  leaderId           int(11) NOT NULL COLLATE utf8_unicode_ci,
  sportId            int(11) NOT NULL COLLATE utf8_unicode_ci,
  groupName          varchar(255) COLLATE utf8_unicode_ci,
  membersName        varchar(255) COLLATE utf8_unicode_ci,
  groupDes           TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (groupId),
  FOREIGN KEY (sportId) REFERENCES Sports(sportId),
  FOREIGN KEY (leaderId) REFERENCES Users(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
MySQL said: Documentation
#1215 - Cannot add foreign key constraint

我知道它是FOREIGN KEY (sportId) REFERENCES Sports(sportId)，行，因为当我删除它时，我没有得到错误，一切都很好。我的运动表可能有问题，或者只是语法错误，但我似乎看不到它。有人请告诉我，我疯了，这是一个简单的语法问题。这是我正在编写的完整数据库文件。

DROP DATABASE if EXISTS sqlfile;
CREATE DATABASE sqlfile;
USE sqlfile;
DROP TABLE if EXISTS Users;
CREATE TABLE Users (
  userId             int(11) NOT NULL AUTO_INCREMENT,
  userName           varchar (255) UNIQUE NOT NULL COLLATE utf8_unicode_ci,
  password           varchar(255) COLLATE utf8_unicode_ci,
  firstName          varchar(255) COLLATE utf8_unicode_ci,
  lastName           varchar(255) COLLATE utf8_unicode_ci,
  tel                char(10) COLLATE utf8_unicode_ci,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Groups (
  groupId            int(11) NOT NULL AUTO_INCREMENT,
  leaderId           int(11) NOT NULL COLLATE utf8_unicode_ci,
  sportId            int(11) NOT NULL COLLATE utf8_unicode_ci,
  groupName          varchar(255) COLLATE utf8_unicode_ci,
  membersName        varchar(255) COLLATE utf8_unicode_ci,
  groupDes           TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (groupId),
  FOREIGN KEY (sportId) REFERENCES Sports(sportId),
  FOREIGN KEY (leaderId) REFERENCES Users(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Members (
  memberId           int(11) NOT NULL AUTO_INCREMENT,
  groupId            int(11) NOT NULL COLLATE utf8_unicode_ci,
  userId             int(11) NOT NULL COLLATE utf8_unicode_ci,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (memberId),
  FOREIGN KEY (groupId) REFERENCES Groups(groupId),
  FOREIGN KEY (userId) REFERENCES Users(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Sports (
  sportId            int(11) NOT NULL AUTO_INCREMENT,
  sportName          varchar(255) COLLATE utf8_unicode_ci,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (sportId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

为了在groups表中有外键引用，您需要首先创建您的sports表

DROP TABLE if EXISTS Users;
CREATE TABLE Users (
....
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Sports (
....
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Groups (
....
  FOREIGN KEY (sportId) REFERENCES Sports(sportId),
  FOREIGN KEY (leaderId) REFERENCES Users(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Members (
....
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

DEMO

查看MySQL Dump输出....

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
[your CREATEs here]
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

除了单独添加约束之外，这是我所知道的唯一其他方法，甚至可以创建以某种循环或互惠方式相互引用的表(而不是您的结构)。

您需要在组之前创建sports表，或者在有sports表之后添加约束。

DROP DATABASE if EXISTS classbash;
CREATE DATABASE classbash;
USE classbash;
DROP TABLE if EXISTS Users;
CREATE TABLE Users (
  userId             int(11) NOT NULL AUTO_INCREMENT,
  userName           varchar (255) UNIQUE NOT NULL COLLATE utf8_unicode_ci,
  password           varchar(255) COLLATE utf8_unicode_ci,
  firstName          varchar(255) COLLATE utf8_unicode_ci,
  lastName           varchar(255) COLLATE utf8_unicode_ci,
  tel                char(10) COLLATE utf8_unicode_ci,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Sports (
  sportId            int(11) NOT NULL AUTO_INCREMENT,
  sportName          varchar(255) COLLATE utf8_unicode_ci,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (sportId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Groups (
  groupId            int(11) NOT NULL AUTO_INCREMENT,
  leaderId           int(11) NOT NULL COLLATE utf8_unicode_ci,
  sportId            int(11) NOT NULL COLLATE utf8_unicode_ci,
  groupName          varchar(255) COLLATE utf8_unicode_ci,
  membersName        varchar(255) COLLATE utf8_unicode_ci,
  groupDes           TEXT CHARACTER SET latin1 COLLATE latin1_general_cs,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (groupId),
  FOREIGN KEY (sportId) REFERENCES Sports(sportId),
  FOREIGN KEY (leaderId) REFERENCES Users(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE Members (
  memberId           int(11) NOT NULL AUTO_INCREMENT,
  groupId            int(11) NOT NULL COLLATE utf8_unicode_ci,
  userId             int(11) NOT NULL COLLATE utf8_unicode_ci,
  dateCreated    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (memberId),
  FOREIGN KEY (groupId) REFERENCES Groups(groupId),
  FOREIGN KEY (userId) REFERENCES Users(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;