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

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

edited Jan 2 at 19:04

The Impaler

11.5k41441

asked Jan 2 at 18:19

MxnaXV0S

Possible duplicate of When to use single quotes, double quotes, and back ticks in MySQL

– Uueerdo
Jan 2 at 18:24

3

Remove quotes from ('id')

– forpas
Jan 2 at 18:25

@forpas After removing the quotes: Can't create table banAPI.bans (errno: 150 "Foreign key constraint is incorrectly formed")

– MxnaXV0S
Jan 2 at 18:34

@MxnaXV0S see this mariadb.com/kb/en/library/foreign-keys

– forpas
Jan 2 at 18:53

add a comment |

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

edited Jan 2 at 19:04

The Impaler

11.5k41441

asked Jan 2 at 18:19

MxnaXV0S

Possible duplicate of When to use single quotes, double quotes, and back ticks in MySQL

– Uueerdo
Jan 2 at 18:24

3

Remove quotes from ('id')

– forpas
Jan 2 at 18:25

@forpas After removing the quotes: Can't create table banAPI.bans (errno: 150 "Foreign key constraint is incorrectly formed")

– MxnaXV0S
Jan 2 at 18:34

@MxnaXV0S see this mariadb.com/kb/en/library/foreign-keys

– forpas
Jan 2 at 18:53

add a comment |

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

edited Jan 2 at 19:04

The Impaler

11.5k41441

asked Jan 2 at 18:19

MxnaXV0S

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

sql mariadb

edited Jan 2 at 19:04

The Impaler

11.5k41441

asked Jan 2 at 18:19

MxnaXV0S

edited Jan 2 at 19:04

The Impaler

11.5k41441

asked Jan 2 at 18:19

MxnaXV0S

edited Jan 2 at 19:04

The Impaler

11.5k41441

edited Jan 2 at 19:04

The Impaler

11.5k41441

edited Jan 2 at 19:04

The Impaler

11.5k41441

asked Jan 2 at 18:19

MxnaXV0S

asked Jan 2 at 18:19

MxnaXV0S

asked Jan 2 at 18:19

MxnaXV0S

Possible duplicate of When to use single quotes, double quotes, and back ticks in MySQL

– Uueerdo
Jan 2 at 18:24

3

Remove quotes from ('id')

– forpas
Jan 2 at 18:25

@forpas After removing the quotes: Can't create table banAPI.bans (errno: 150 "Foreign key constraint is incorrectly formed")

– MxnaXV0S
Jan 2 at 18:34

@MxnaXV0S see this mariadb.com/kb/en/library/foreign-keys

– forpas
Jan 2 at 18:53

add a comment |

Possible duplicate of When to use single quotes, double quotes, and back ticks in MySQL

– Uueerdo
Jan 2 at 18:24

3

Remove quotes from ('id')

– forpas
Jan 2 at 18:25

@forpas After removing the quotes: Can't create table banAPI.bans (errno: 150 "Foreign key constraint is incorrectly formed")

– MxnaXV0S
Jan 2 at 18:34

@MxnaXV0S see this mariadb.com/kb/en/library/foreign-keys

– forpas
Jan 2 at 18:53

Possible duplicate of When to use single quotes, double quotes, and back ticks in MySQL

– Uueerdo
Jan 2 at 18:24

Remove quotes from ('id')

– forpas
Jan 2 at 18:25

@forpas After removing the quotes: Can't create table banAPI.bans (errno: 150 "Foreign key constraint is incorrectly formed")

– MxnaXV0S
Jan 2 at 18:34

@MxnaXV0S see this mariadb.com/kb/en/library/foreign-keys

– forpas
Jan 2 at 18:53

add a comment |

2 Answers
2

active

oldest

votes

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 2 at 19:37

answered Jan 2 at 18:37

GMB

19.7k41028

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 2 at 19:03

@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information

– GMB
Jan 2 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 2 at 22:43

Welcome @MxnaXVS0S

– GMB
Jan 2 at 23:14

add a comment |

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

);

answered Jan 2 at 19:04

The Impaler

11.5k41441

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54011259%2fmariadb-cant-create-a-table-with-two-foreign-keys%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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)

...

edited Jan 2 at 19:37

answered Jan 2 at 18:37

GMB

19.7k41028

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 2 at 19:03

@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information

– GMB
Jan 2 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 2 at 22:43

Welcome @MxnaXVS0S

– GMB
Jan 2 at 23:14

add a comment |

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)

...

edited Jan 2 at 19:37

answered Jan 2 at 18:37

GMB

19.7k41028

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 2 at 19:03

@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information

– GMB
Jan 2 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 2 at 22:43

Welcome @MxnaXVS0S

– GMB
Jan 2 at 23:14

add a comment |

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)

...

edited Jan 2 at 19:37

answered Jan 2 at 18:37

GMB

19.7k41028

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)

...

edited Jan 2 at 19:37

answered Jan 2 at 18:37

GMB

19.7k41028

edited Jan 2 at 19:37

answered Jan 2 at 18:37

GMB

19.7k41028

answered Jan 2 at 18:37

GMB

19.7k41028

answered Jan 2 at 18:37

GMB

19.7k41028

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 2 at 19:03

@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information

– GMB
Jan 2 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 2 at 22:43

Welcome @MxnaXVS0S

– GMB
Jan 2 at 23:14

add a comment |

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 2 at 19:03

@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information

– GMB
Jan 2 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 2 at 22:43

Welcome @MxnaXVS0S

– GMB
Jan 2 at 23:14

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 2 at 19:03

@MxnaXV0S : it works... I added a db fiddle to my answer, along with more information

– GMB
Jan 2 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 2 at 22:43

Welcome @MxnaXVS0S

– GMB
Jan 2 at 23:14

add a comment |

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

);

answered Jan 2 at 19:04

The Impaler

11.5k41441

add a comment |

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

);

answered Jan 2 at 19:04

The Impaler

11.5k41441

add a comment |

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

);

answered Jan 2 at 19:04

The Impaler

11.5k41441

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

);

answered Jan 2 at 19:04

The Impaler

11.5k41441

answered Jan 2 at 19:04

The Impaler

11.5k41441

answered Jan 2 at 19:04

The Impaler

11.5k41441

answered Jan 2 at 19:04

The Impaler

11.5k41441

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu