How do I create a mysql table with foreign keys?

Question

I am just trying to create a table with two foreign keys. The other two tables, users and puzzles, already have primary keys user_id and puzzle_id

CREATE TABLE `bglascoc_puzzle_db`.`comments` (
  `comment_id` INT NOT NULL,
  `comment` VARCHAR(144) NULL,
  `user_id` INT NOT NULL,
  `puzzle_id` INT NOT NULL,
  PRIMARY KEY (`comment_id`),
  INDEX `user_id` (`user_id` ASC) INVISIBLE,
  INDEX `puzzle_id` (`puzzle_id` ASC) VISIBLE,
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `bglascoc_puzzle_db`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `puzzle_id`
    FOREIGN KEY (`puzzle_id`)
    REFERENCES `bglascoc_puzzle_db`.`puzzles` (`puzzle_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

However, I get the error "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 '"

Does this answer your question? [MySQL error 1064 syntax but everything seems fine](https://stackoverflow.com/questions/50393245/mysql-error-1064-syntax-but-everything-seems-fine) — P.Salmon, Nov 02 '20 at 10:13
The SQL code works on MySQL 8.0 https://sqlize.online/?phpses=null&sqlses=6c6ac4c707c6c9157540a8160af058e3&php_version=null&sql_version=mysql80 — Slava Rozhnev, Nov 02 '20 at 10:14

score 1 · Answer 1 · answered Nov 02 '20 at 10:24

You are running MariaDB, not MySQL, as the error message indicates. Unlike MySQL, which added this feature in version 8.0, MariaDB just does not support invisible indexes. You need to remove that keyword.

I would also argue that ASC is the default sort direction for indexes, so this keyword is actually superfluous.

So:

CREATE TABLE `bglascoc_puzzle_db`.`comments` (
  `comment_id` INT NOT NULL,
  `comment` VARCHAR(144) NULL,
  `user_id` INT NOT NULL,
  `puzzle_id` INT NOT NULL,
  PRIMARY KEY (`comment_id`),
  INDEX `user_id` (`user_id`),
  INDEX `puzzle_id` (`puzzle_id`),
  CONSTRAINT `user_id`
    FOREIGN KEY (`user_id`)
    REFERENCES `bglascoc_puzzle_db`.`users` (`user_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `puzzle_id`
    FOREIGN KEY (`puzzle_id`)
    REFERENCES `bglascoc_puzzle_db`.`puzzles` (`puzzle_id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

The declaration of the foreign key constraints look fine (as long as the parent columns exist, are indexed, and have a matching datatype - which can't be told from the information you provided).

score 0 · Answer 2 · answered Nov 02 '20 at 10:32

0

The syntax problem is the keyword INVISIBLE and VISIBLE in the Index, remove these and it will work

answered Nov 02 '20 at 10:32

Holzer

59
8

How do I create a mysql table with foreign keys?

2 Answers2