php - 错误的外键约束

Question

我正在尝试调试一个简单的 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;

Answer 1

您需要先创建运动表，以便在组表中引用外键

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