Mysql外键错误无法修复

Question

我是 mysql 新手，从 mysql 数据库编写 sql 脚本，我的大多数表都可以工作，但有些则不能，并且 mysql 错误尽可能不伦不类。这可能与多个外键有关。

 create table Users (
    id int not null auto_increment,
    type enum('t1', 't2', 't3') not null,
    name char(30) not null,
    phone char(15),
    email char(30),
    username char(30),
    password char(30),
    created timestamp,
    primary key (id)
);

create table Groups (
    id int not null auto_increment,
    user int not null,
    name char(30) not null,
    phone char(15),
    email char(30),
    created timestamp,
    primary key (id),
    foreign key (user) references Users(id)
);

create table Group_Members (
    id int not null auto_increment,
    group int not null,
    user int not null,
    created timestamp,
    primary key (id),
    foreign key (group) references Groups(id),
    foreign key (user) references Users(id)
);

Answer 1

Group 是 MySQL 中的保留字。您必须使用 ` 来引用查询中的保留字。

试试这个:

create table Users (
    id int not null auto_increment,
    type enum('t1', 't2', 't3') not null,
    name char(30) not null,
    phone char(15),
    email char(30),
    username char(30),
    password char(30),
    created timestamp,
    primary key (id)
);

create table Groups (
    id int not null auto_increment,
    user int not null,
    name char(30) not null,
    phone char(15),
    email char(30),
    created timestamp,
    primary key (id),
    foreign key (user) references Users(id)
);

create table Group_Members (
    id int not null auto_increment,
    `group` int not null,
    user int not null,
    created timestamp,
    primary key (id),
    foreign key (`group`) references Groups(id),
    foreign key (user) references Users(id)
);