MariaDB ERROR 4161 (HY000): Unknown Data Type

Question

尝试使用外键创建表时出现未知数据类型错误。我不确定我的语法是否错误?确切的错误消息是: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
);

Answer 1

错误是在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
);