MariaDB - can't create a table with two foreign keys

Question

I have following query:

create table bans
(
    id int auto_increment primary key ,
    reason int not null,
    player int not null,
    server int not null,
    starts timestamp default current_timestamp not null,
    ends DATETIME not null,
    constraint bans__fk_player
        foreign key (player) references players ('id'),
    constraint bans__fk_server
        foreign key (server) references servers ('id')
);

Which results in:

[2019-01-02 18:35:29] [42000][1064] (conn=75) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''id'),
[2019-01-02 18:35:29] [42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''id'),

---

I just want to make 1:n relation between:

players.id -> bans.player and name it bans__fk_player

servers.id -> bans.server and name it bans__fk_server

Answer 1

1) This was commented already by SO folks : you need to remove the quotes around your identifiers in the foreign keys definition. Also see this SO post for a general discussion about using quotes in mysql/MariaDB.

2) Another issue is that you are not defining the constraint properly, it is missing a name for the foreign key. The syntax is as folllows, as explained in this mysql turorial :

CONSTRAINT constraint_name
FOREIGN KEY foreign_key_name (columns)
REFERENCES parent_table(columns)

So your code should be :

...
constraint bans__player
    foreign key bans__fk_player (player) references players (id),
constraint bans__server
    foreign key bans__fk_server (server) references servers (id)
...

See this db fiddle.

This should work as well, and produces a shorter syntax (you probably don’t need to explicitly name the constraints) :

...
foreign key bans__fk_player (player) references players (id),
foreign key bans__fk_server (server) references servers (id)
...

If you are still experiencing errors, then you have to look at the definition of the referenced tables (servers and players). In both tables, id must be either the primary key of the table, or must be controlled by a unique constraint. And of course, it must be numeric.