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

Possible duplicate of [When to use single quotes, double quotes, and back ticks in MySQL](https://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-back-ticks-in-mysql) — Uueerdo, Jan 02 '19 at 18:24
@forpas After removing the quotes: Can't create table `banAPI`.`bans` (errno: 150 "Foreign key constraint is incorrectly formed") — MxnaXV0S, Jan 02 '19 at 18:34
@MxnaXV0S see this https://mariadb.com/kb/en/library/foreign-keys/ — forpas, Jan 02 '19 at 18:53

GMB · Accepted Answer · 2019-01-02T19:37:47.657

2

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.

edited Jan 02 '19 at 19:37

answered Jan 02 '19 at 18:37

GMB

216,147
25
84
135

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__player foreign key bans__fk_player (player) references players (id), constraint bans__server foreign key bans__fk_server (server) references servers (id) ); ERROR 1005 (HY000): Can't create table `banAPI`.`bans` (errno: 150 "Foreign key constraint is incorrectly formed") – MxnaXV0S Jan 02 '19 at 19:03
@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information – GMB Jan 02 '19 at 19:38
@MxnaXV0S : Ok, It seems silly but server.id was named server.server_id and that was causing the issue. Thanks for help! – MxnaXV0S Jan 02 '19 at 22:43
Welcome @MxnaXVS0S – GMB Jan 02 '19 at 23:14

score 0 · Answer 2 · answered Jan 02 '19 at 19:04

If you want to quote the column id, in MariaDB (also in MySQL) you should use "back ticks", as in:

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

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

2 Answers2