Why doesn't init script create every table in the script?-CodePudding

I am trying to make an init script for MariaDB, using docker-compose.

Only the 3 first tables are created, so player_team_relation and player table are not created.

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

create table if not exists player_team_relation
(
    player_id bigint not null primary key,
    team_id   bigint not null primary key,
    constraint player_ptr_constraint foreign key (player_id) references player (player_id),
    constraint team_ptr_constraint foreign key (team_id) references team (team_id)
);


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

I have tried creating the database many times by deleting the volume and creating a new container.

CodePudding user response：

Another example why error checking is always recommended:

The statement

create table if not exists player_team_relation
(
    player_id bigint not null primary key,
    team_id   bigint not null primary key,
    ...

will fail, since it's not possible to define multiple primary keys per table. For more information on primary keys please check the documentation.

About your column definitions: Are you sure you want to use BIGINT (signed integer) instead of BIGINT UNSIGNED for auto_increment? Do you really need BIGINT? The maximum value of INTEGER UNSIGNED is 4294967295 - more than half of the world's population.