为何Docker-Compose下MariaDB初始化脚本未创建全部表？

核心问题1：外键依赖顺序颠倒 #

player_team_relation表的外键player_id需要引用player表，但你先创建了关联表，后创建被依赖的player表。数据库执行脚本是按从上到下的顺序，此时player表还不存在，外键约束创建失败，直接导致player_team_relation表创建失败，部分环境下后续脚本也会中断执行，所以player表也没被创建。

核心问题2：复合主键语法错误 #

player_team_relation表中你写了两个独立的primary key，这不符合MariaDB的语法规范。复合主键必须把多个字段放在同一个primary key()括号内定义，否则会触发语法错误，这也是该表创建失败的原因之一。

修正后的初始化脚本 #

create table if not exists user
(
    user_id       varchar(255) primary key,
    username      varchar(30)  not null,
    password_hash varchar(255) not null
);

create table if not exists history
(
    history_id bigint auto_increment primary key,
    user_id    varchar(255) not null,
    game_date  timestamp    not null,
    constraint user_history_constraint foreign key (user_id) references user (user_id)
);

create table if not exists team
(
    team_id     bigint auto_increment primary key,
    history_id  bigint      not null,
    player_name varchar(30) not null,
    score       bigint      not null,
    hasWon      bool        not null,
    constraint history_team_constraint foreign key (history_id) references history (history_id)
);

-- 调整顺序：先创建被依赖的player表
create table if not exists player
(
    player_id   bigint auto_increment primary key,
    user_id     varchar(255) not null,
    player_name varchar(30) not null,
    wins        int         not null,
    losses      int         not null,
    score       bigint      not null,
    constraint user_player_constraint foreign key (user_id) references user (user_id)
);

-- 修正复合主键定义，确保依赖表已存在
create table if not exists player_team_relation
(
    player_id bigint not null,
    team_id   bigint not null,
    primary key (player_id, team_id), -- 复合主键正确写法
    constraint player_ptr_constraint foreign key (player_id) references player (player_id),
    constraint team_ptr_constraint foreign key (team_id) references team (team_id)
);

操作步骤 #

1. 彻底清理现有容器和数据卷：

   docker-compose down -v
   

2. 将上述修正后的脚本替换原来的初始化文件
3. 重新启动容器：

   docker-compose up -d
   

内容的提问来源于stack exchange，提问作者blueDragon