尝试使用外键创建表时出现未知数据类型错误。我不确定我的语法是否错误?确切的错误消息是:error 4161 (HY000)在第14行:Unknown data type: 't_name',这意味着P1_Player表是抛出错误的地方。
DROP TABLE IF EXISTS P1_Player;
DROP TABLE IF EXISTS P1_Plays;
DROP TABLE IF EXISTS P1_Coach;
DROP TABLE IF EXISTS P1_Game;
DROP TABLE IF EXISTS P1_Team;
create table P1_Team
(t_name varchar(20),
primary key (t_name)
);
create table P1_Player
(fname varchar(20),
lname varchar(20),
DOB date,
jersey_# int,
t_name varchar(20),
primary key (fname, lname, DOB),
foreign key (t_name) references P1_Team (t_name)
on delete cascade
);
create table P1_Coach
(fname varchar(20),
lname varchar(20),
t_name varchar(20),
foreign key (t_name) references P1_Team (t_name)
on delete cascade
);
create table P1_Game
(t_name varchar(20),
game_date date,
game_time time,
primary key (t_name, game_date, game_time),
foreign key (t_name) references P1_Team (t_name)
on delete cascade
);
create table P1_Plays
(t_name varchar(20),
game_date date,
game_time time,
primary key (t_name, game_date, game_time),
foreign key (t_name, game_date, game_time) references P1_Game (t_name, game_date, game_time)
on delete cascade
);
错误是在jersey_#
上create table P1_Player
,你应该使用反引号('),如果你想在mysql/mariadb列名中使用特殊字符:
create table P1_Player
(fname varchar(20),
lname varchar(20),
DOB date,
`jersey_#` int,
t_name varchar(20),
primary key (fname, lname, DOB),
foreign key (t_name) references P1_Team (t_name)
on delete cascade
);