sql—不明白为什么mysql数据库语法不能编译

edqdpe6u  于 2021-06-19  发布在  Mysql
关注(0)|答案(2)|浏览(391)

我得到这个语法错误的一个数据库,我为自己的个人项目写,不确定为什么会发生这个错误,任何帮助将不胜感激!此时所需的结果只是编译,错误是一个简单的语法错误。
问题表是团队表。
错误代码:1215:无法添加外键约束。

-- CREATE DATABASE basketBall;
  DROP TABLE LEAGUE;
 -- DROP TABLE TEAM;
  DROP TABLE SESSION;
CREATE TABLE LEAGUE (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL UNIQUE,
    PRIMARY KEY(id)
);

CREATE TABLE SESSION (
    year INT NOT NULL,
    season VARCHAR(50) NOT NULL,
    division VARCHAR(5) NOT NULL,
    PRIMARY KEY(year, season, division),
    CONSTRAINT chk_season CHECK (season IN ('Fall', 'Winter', 'Spring', 'Summer'))
);

CREATE TABLE TEAM (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    season VARCHAR(50) NOT NULL,
    year INT NOT NULL,
    division VARCHAR(5) NOT NULL,
    PRIMARY KEY(id),
    FOREIGN KEY(season) REFERENCES SESSION(season),
    FOREIGN KEY(year) REFERENCES SESSION(year),
    FOREIGN KEY(division) REFERENCES SESSION(division)   

);

CREATE TABLE PLAYER (
    id INT NOT NULL AUTO_INCREMENT,
    fname VARCHAR(30) NOT NULL,
    lname VARCHAR(30) NOT NULL,
    lid INT,
    PRIMARY KEY(id)
);

CREATE TABLE GAME (
    id INT NOT NULL AUTO_INCREMENT,
    time VARCHAR(5),
    court VARCHAR(20),
    date DATE,
    PRIMARY KEY(id)
);

CREATE TABLE STATS  (
    pid INT NOT NULL,
    gid int NOT NULL,
    pts INT NOT NULL,
    fgm INT NOT NULL,
    fga INT NOT NULL,
    fta INT NOT NULL,
    ftm INT NOT NULL,
    3fgm INT NOT NULL,
    3fga INT NOT NULL,
    oreb INT NOT NULL,
    dreb INT NOT NULL,
    ast INT NOT NULL,
    stl INT NOT NULL,
    blk INT NOT NULL,
    turnover INT NOT NULL,
    eff INT NOT NULL,
    pf INT NOT NULL,
    min INT NOT NULL,
    PRIMARY KEY(pid, gid),
    FOREIGN KEY(pid) REFERENCES PLAYER(id),
    FOREIGN KEY(gid) REFERENCES GAME(id)
);

CREATE TABLE Players_on_Team (
    tid INT NOT NULL,
    pid INT NOT NULL,
    PRIMARY KEY(tid, pid),
    FOREIGN KEY(tid) REFERENCES TEAM(id)

);

CREATE TABLE League_Sessions (
    lid INT NOT NULL,
    year INT NOT NULL,
    season VARCHAR(50) NOT NULL,
    division VARCHAR(5) NOT NULL,
    PRIMARY KEY(lid, year, season, division),
    FOREIGN KEY(lid) REFERENCES LEAGUE(id) 
);
kknvjkwl

kknvjkwl1#

我只是试了一下,它执行起来没有任何错误。

zdwk9cvp

zdwk9cvp2#

在外键中引用的列必须编制索引。这两个外键 TEAM :

FOREIGN KEY(season) REFERENCES SESSION(season),
FOREIGN KEY(division) REFERENCES SESSION(division)

引用没有自己索引的列。它们是多列索引的一部分,但只有多列索引的前缀充当这些特定列的索引。
您可以在 season 以及 division 列到 SESSION table。但可能更适合创建多列外键:

FOREIGN KEY (year, season, division) REFERENCES SESSION(year, season, division)

相关问题