CODEWITHSUNDEEP
mysql
adam
adam

Reputation: 51

Mysql foreign key errors can't fix

I am new to mysql, and writing a sql script from a mysql database, most of my tables work, but some won't, and the mysql error is about as nondescript as possible. It might be something to do with multiple foreign keys.

 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)
);

Upvotes: 0

Views: 36

Answers (1)

Ogreucha
Ogreucha

Reputation: 688

Group is reserved word in MySQL. You have to use ` to quote reserved words in your queries.

Try this:

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)
);

Upvotes: 1

Related Questions