Mixing
SQL Script can't create some tables
-2
My SQL script firstly says that it is not connected, when i open it, so i connect it by going to Query -> Reconnect to server, which works. Then i try to execute the SQL script which only creates the first two tables are met with an error code 1064, and the errors are at the VISIBLE Index lines. I have no idea what that means as i am completely new to SQL, can anyone help?
Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
-- MySQL Script generated by MySQL Workbench
-- Wed Jan 2 02:21:44 2019
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_review` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `game_review` ;
-- -----------------------------------------------------
-- Table `game_review`.`games`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`games` (
`game_Id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`tags` VARCHAR(60) NOT NULL,
`systemReq` VARCHAR(255) NOT NULL,
`developer` VARCHAR(45) NOT NULL,
`publisher` VARCHAR(45) NOT NULL,
`trailer` VARCHAR(45) NULL,
`about` VARCHAR(255) NULL,
`platform` VARCHAR(45) NOT NULL,
`categories` VARCHAR(45) NOT NULL,
`description` VARCHAR(45) NOT NULL,
`releaseDate` DATETIME NOT NULL,
PRIMARY KEY (`game_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`users` (
`user_Id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
PRIMARY KEY (`user_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`reviews`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`reviews` (
`review_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`review` TEXT(1024) NOT NULL,
`rating` INT NULL,
`dateposted` DATETIME NULL,
PRIMARY KEY (`review_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`favourites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`favourites` (
`favourites_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
PRIMARY KEY (`favourites_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`forum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`forum` (
`forum_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`topic` VARCHAR(45) NOT NULL,
`views` INT NOT NULL,
`likes` INT NOT NULL,
`comment` TEXT(300) NULL,
`totalComments` INT NULL,
`dateposted` DATETIME NOT NULL,
PRIMARY KEY (`forum_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`game cart`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`game cart` (
`cart_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`quantity` INT NULL,
PRIMARY KEY (`cart_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
mysql database server
edited Jan 2 at 7:18
marc_s
581k13011211268
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
add a comment |
-2
My SQL script firstly says that it is not connected, when i open it, so i connect it by going to Query -> Reconnect to server, which works. Then i try to execute the SQL script which only creates the first two tables are met with an error code 1064, and the errors are at the VISIBLE Index lines. I have no idea what that means as i am completely new to SQL, can anyone help?
Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
-- MySQL Script generated by MySQL Workbench
-- Wed Jan 2 02:21:44 2019
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_review` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `game_review` ;
-- -----------------------------------------------------
-- Table `game_review`.`games`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`games` (
`game_Id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`tags` VARCHAR(60) NOT NULL,
`systemReq` VARCHAR(255) NOT NULL,
`developer` VARCHAR(45) NOT NULL,
`publisher` VARCHAR(45) NOT NULL,
`trailer` VARCHAR(45) NULL,
`about` VARCHAR(255) NULL,
`platform` VARCHAR(45) NOT NULL,
`categories` VARCHAR(45) NOT NULL,
`description` VARCHAR(45) NOT NULL,
`releaseDate` DATETIME NOT NULL,
PRIMARY KEY (`game_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`users` (
`user_Id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
PRIMARY KEY (`user_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`reviews`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`reviews` (
`review_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`review` TEXT(1024) NOT NULL,
`rating` INT NULL,
`dateposted` DATETIME NULL,
PRIMARY KEY (`review_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`favourites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`favourites` (
`favourites_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
PRIMARY KEY (`favourites_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`forum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`forum` (
`forum_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`topic` VARCHAR(45) NOT NULL,
`views` INT NOT NULL,
`likes` INT NOT NULL,
`comment` TEXT(300) NULL,
`totalComments` INT NULL,
`dateposted` DATETIME NOT NULL,
PRIMARY KEY (`forum_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`game cart`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`game cart` (
`cart_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`quantity` INT NULL,
PRIMARY KEY (`cart_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
mysql database server
edited Jan 2 at 7:18
marc_s
581k13011211268
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
mysql 1064 is a syntax error. Please post the complete error message that you are getting
– GMB
Jan 1 at 18:34
Here is the error code. Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
– Xavier Lim Jun Yi
Jan 1 at 18:35
It didn't work sorry
– Xavier Lim Jun Yi
Jan 1 at 18:50
add a comment |
-2
-2
-2
My SQL script firstly says that it is not connected, when i open it, so i connect it by going to Query -> Reconnect to server, which works. Then i try to execute the SQL script which only creates the first two tables are met with an error code 1064, and the errors are at the VISIBLE Index lines. I have no idea what that means as i am completely new to SQL, can anyone help?
Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
-- MySQL Script generated by MySQL Workbench
-- Wed Jan 2 02:21:44 2019
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_review` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `game_review` ;
-- -----------------------------------------------------
-- Table `game_review`.`games`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`games` (
`game_Id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`tags` VARCHAR(60) NOT NULL,
`systemReq` VARCHAR(255) NOT NULL,
`developer` VARCHAR(45) NOT NULL,
`publisher` VARCHAR(45) NOT NULL,
`trailer` VARCHAR(45) NULL,
`about` VARCHAR(255) NULL,
`platform` VARCHAR(45) NOT NULL,
`categories` VARCHAR(45) NOT NULL,
`description` VARCHAR(45) NOT NULL,
`releaseDate` DATETIME NOT NULL,
PRIMARY KEY (`game_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`users` (
`user_Id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
PRIMARY KEY (`user_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`reviews`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`reviews` (
`review_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`review` TEXT(1024) NOT NULL,
`rating` INT NULL,
`dateposted` DATETIME NULL,
PRIMARY KEY (`review_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`favourites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`favourites` (
`favourites_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
PRIMARY KEY (`favourites_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`forum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`forum` (
`forum_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`topic` VARCHAR(45) NOT NULL,
`views` INT NOT NULL,
`likes` INT NOT NULL,
`comment` TEXT(300) NULL,
`totalComments` INT NULL,
`dateposted` DATETIME NOT NULL,
PRIMARY KEY (`forum_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`game cart`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`game cart` (
`cart_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`quantity` INT NULL,
PRIMARY KEY (`cart_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
mysql database server
edited Jan 2 at 7:18
marc_s
581k13011211268
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
My SQL script firstly says that it is not connected, when i open it, so i connect it by going to Query -> Reconnect to server, which works. Then i try to execute the SQL script which only creates the first two tables are met with an error code 1064, and the errors are at the VISIBLE Index lines. I have no idea what that means as i am completely new to SQL, can anyone help?
Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
-- MySQL Script generated by MySQL Workbench
-- Wed Jan 2 02:21:44 2019
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema game_review
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `game_review` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;
USE `game_review` ;
-- -----------------------------------------------------
-- Table `game_review`.`games`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`games` (
`game_Id` INT NOT NULL AUTO_INCREMENT,
`title` VARCHAR(255) NOT NULL,
`tags` VARCHAR(60) NOT NULL,
`systemReq` VARCHAR(255) NOT NULL,
`developer` VARCHAR(45) NOT NULL,
`publisher` VARCHAR(45) NOT NULL,
`trailer` VARCHAR(45) NULL,
`about` VARCHAR(255) NULL,
`platform` VARCHAR(45) NOT NULL,
`categories` VARCHAR(45) NOT NULL,
`description` VARCHAR(45) NOT NULL,
`releaseDate` DATETIME NOT NULL,
PRIMARY KEY (`game_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`users`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`users` (
`user_Id` INT NOT NULL AUTO_INCREMENT,
`username` VARCHAR(45) NOT NULL,
`password` VARCHAR(45) NOT NULL,
`email` VARCHAR(45) NOT NULL,
PRIMARY KEY (`user_Id`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`reviews`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`reviews` (
`review_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`review` TEXT(1024) NOT NULL,
`rating` INT NULL,
`dateposted` DATETIME NULL,
PRIMARY KEY (`review_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`favourites`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`favourites` (
`favourites_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
PRIMARY KEY (`favourites_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE CASCADE
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`forum`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`forum` (
`forum_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`userId` INT NOT NULL,
`topic` VARCHAR(45) NOT NULL,
`views` INT NOT NULL,
`likes` INT NOT NULL,
`comment` TEXT(300) NULL,
`totalComments` INT NULL,
`dateposted` DATETIME NOT NULL,
PRIMARY KEY (`forum_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `userId`
FOREIGN KEY (`userId`)
REFERENCES `game_review`.`users` (`user_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `game_review`.`game cart`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `game_review`.`game cart` (
`cart_Id` INT NOT NULL AUTO_INCREMENT,
`gameId` INT NOT NULL,
`quantity` INT NULL,
PRIMARY KEY (`cart_Id`),
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
CONSTRAINT `gameId`
FOREIGN KEY (`gameId`)
REFERENCES `game_review`.`games` (`game_Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
mysql database server
mysql database server
edited Jan 2 at 7:18
marc_s
581k13011211268
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
edited Jan 2 at 7:18
marc_s
581k13011211268
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
edited Jan 2 at 7:18
marc_s
581k13011211268
edited Jan 2 at 7:18
marc_s
581k13011211268
edited Jan 2 at 7:18
marc_s
581k13011211268
581k13011211268
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
asked Jan 1 at 18:28
Xavier Lim Jun YiXavier Lim Jun Yi
13
13
mysql 1064 is a syntax error. Please post the complete error message that you are getting
– GMB
Jan 1 at 18:34
Here is the error code. Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
– Xavier Lim Jun Yi
Jan 1 at 18:35
It didn't work sorry
– Xavier Lim Jun Yi
Jan 1 at 18:50
add a comment |
mysql 1064 is a syntax error. Please post the complete error message that you are getting
– GMB
Jan 1 at 18:34
Here is the error code. Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
– Xavier Lim Jun Yi
Jan 1 at 18:35
It didn't work sorry
– Xavier Lim Jun Yi
Jan 1 at 18:50
mysql 1064 is a syntax error. Please post the complete error message that you are getting
– GMB
Jan 1 at 18:34
mysql 1064 is a syntax error. Please post the complete error message that you are getting
– GMB
Jan 1 at 18:34
Here is the error code. Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
– Xavier Lim Jun Yi
Jan 1 at 18:35
Here is the error code. Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
– Xavier Lim Jun Yi
Jan 1 at 18:35
It didn't work sorry
– Xavier Lim Jun Yi
Jan 1 at 18:50
It didn't work sorry
– Xavier Lim Jun Yi
Jan 1 at 18:50
add a comment |
1 Answer
1
active
oldest
votes
1
Your declaration of indexes is not correct : VISIBLE is not recognized syntax. Also, the ASC mention is superfluous as it corrresponds to the default.
Replace :
...
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
...
With :
...
INDEX `reviews_gameId_idx` (`gameId`),
INDEX `reviews_userId_idx` (`userId`),
...
For more information see the mysql CREATE INDEX docs.
Also please bear in mind that, in most RDBMS others than mysql, index names are global across the database schema, hence make sure not to use the same index name twice (I can see that at least index gameId_idx is declared twice. One solution is to prefix the index name with the table it belongs to, as shown above.
Foreign key names between different tables also must be unique (you have duplicates like game_Id).
Finally I also notice that one of your table name has a space in it (game cart) : this is not a good practice, should be avoided.
answered Jan 1 at 18:45
GMBGMB
17.2k3928
The error 1064 went away then i got Error Code: 1005. Can't create tablegame_review.game cart(errno: 121 "Duplicate key on write or update")
– Xavier Lim Jun Yi
Jan 1 at 18:57
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53997897%2fsql-script-cant-create-some-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
1
Your declaration of indexes is not correct : VISIBLE is not recognized syntax. Also, the ASC mention is superfluous as it corrresponds to the default.
Replace :
...
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
...
With :
...
INDEX `reviews_gameId_idx` (`gameId`),
INDEX `reviews_userId_idx` (`userId`),
...
For more information see the mysql CREATE INDEX docs.
Also please bear in mind that, in most RDBMS others than mysql, index names are global across the database schema, hence make sure not to use the same index name twice (I can see that at least index gameId_idx is declared twice. One solution is to prefix the index name with the table it belongs to, as shown above.
Foreign key names between different tables also must be unique (you have duplicates like game_Id).
Finally I also notice that one of your table name has a space in it (game cart) : this is not a good practice, should be avoided.
answered Jan 1 at 18:45
GMBGMB
17.2k3928
The error 1064 went away then i got Error Code: 1005. Can't create tablegame_review.game cart(errno: 121 "Duplicate key on write or update")
– Xavier Lim Jun Yi
Jan 1 at 18:57
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
add a comment |
1
Your declaration of indexes is not correct : VISIBLE is not recognized syntax. Also, the ASC mention is superfluous as it corrresponds to the default.
Replace :
...
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
...
With :
...
INDEX `reviews_gameId_idx` (`gameId`),
INDEX `reviews_userId_idx` (`userId`),
...
For more information see the mysql CREATE INDEX docs.
Also please bear in mind that, in most RDBMS others than mysql, index names are global across the database schema, hence make sure not to use the same index name twice (I can see that at least index gameId_idx is declared twice. One solution is to prefix the index name with the table it belongs to, as shown above.
Foreign key names between different tables also must be unique (you have duplicates like game_Id).
Finally I also notice that one of your table name has a space in it (game cart) : this is not a good practice, should be avoided.
answered Jan 1 at 18:45
GMBGMB
17.2k3928
The error 1064 went away then i got Error Code: 1005. Can't create tablegame_review.game cart(errno: 121 "Duplicate key on write or update")
– Xavier Lim Jun Yi
Jan 1 at 18:57
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
add a comment |
1
1
1
Your declaration of indexes is not correct : VISIBLE is not recognized syntax. Also, the ASC mention is superfluous as it corrresponds to the default.
Replace :
...
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
...
With :
...
INDEX `reviews_gameId_idx` (`gameId`),
INDEX `reviews_userId_idx` (`userId`),
...
For more information see the mysql CREATE INDEX docs.
Also please bear in mind that, in most RDBMS others than mysql, index names are global across the database schema, hence make sure not to use the same index name twice (I can see that at least index gameId_idx is declared twice. One solution is to prefix the index name with the table it belongs to, as shown above.
Foreign key names between different tables also must be unique (you have duplicates like game_Id).
Finally I also notice that one of your table name has a space in it (game cart) : this is not a good practice, should be avoided.
answered Jan 1 at 18:45
GMBGMB
17.2k3928
Your declaration of indexes is not correct : VISIBLE is not recognized syntax. Also, the ASC mention is superfluous as it corrresponds to the default.
Replace :
...
INDEX `gameId_idx` (`gameId` ASC) VISIBLE,
INDEX `userId_idx` (`userId` ASC) VISIBLE,
...
With :
...
INDEX `reviews_gameId_idx` (`gameId`),
INDEX `reviews_userId_idx` (`userId`),
...
For more information see the mysql CREATE INDEX docs.
Also please bear in mind that, in most RDBMS others than mysql, index names are global across the database schema, hence make sure not to use the same index name twice (I can see that at least index gameId_idx is declared twice. One solution is to prefix the index name with the table it belongs to, as shown above.
Foreign key names between different tables also must be unique (you have duplicates like game_Id).
Finally I also notice that one of your table name has a space in it (game cart) : this is not a good practice, should be avoided.
answered Jan 1 at 18:45
GMBGMB
17.2k3928
edited Jan 1 at 21:18
answered Jan 1 at 18:45
GMBGMB
17.2k3928
answered Jan 1 at 18:45
GMBGMB
17.2k3928
answered Jan 1 at 18:45
GMBGMB
17.2k3928
17.2k3928
The error 1064 went away then i got Error Code: 1005. Can't create tablegame_review.game cart(errno: 121 "Duplicate key on write or update")
– Xavier Lim Jun Yi
Jan 1 at 18:57
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
add a comment |
The error 1064 went away then i got Error Code: 1005. Can't create tablegame_review.game cart(errno: 121 "Duplicate key on write or update")
– Xavier Lim Jun Yi
Jan 1 at 18:57
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
The error 1064 went away then i got Error Code: 1005. Can't create table
game_review.game cart (errno: 121 "Duplicate key on write or update")– Xavier Lim Jun Yi
Jan 1 at 18:57
The error 1064 went away then i got Error Code: 1005. Can't create table
game_review.game cart (errno: 121 "Duplicate key on write or update")– Xavier Lim Jun Yi
Jan 1 at 18:57
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
What should i do to fix it then? Completely change the name of the indexes (I don't know the impacts of that) or remove the index names? I'm so sorry for all these questions as i literally just started to use SQL. It is weird that these errors still appeared despite the script being forward engineered from Workbench itself.
– Xavier Lim Jun Yi
Jan 1 at 20:12
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
@XavierLimJunYi : this one is related to unicorn of foreign keys... answer updated...
– GMB
Jan 1 at 21:20
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');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53997897%2fsql-script-cant-create-some-tables%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
mysql 1064 is a syntax error. Please post the complete error message that you are getting
– GMB
Jan 1 at 18:34
Here is the error code. Error Code: 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 ' INDEX userId_idx (userId ASC) VISIBLE, CONSTRAINT gameId FOREIGN ' at line 9
– Xavier Lim Jun Yi
Jan 1 at 18:35
It didn't work sorry
– Xavier Lim Jun Yi
Jan 1 at 18:50