Good Evening, So I was creating a SQL model and I reached a syntax error. The program shows:
Executing SQL script in server
ERROR: Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DEFAULT' at line 19
SQL Code:
CREATE TABLE IF NOT EXISTS `fmr19a`.`Registers` (
`reg_cost` DECIMAL(5,2) NOT NULL,
`reg_date` DATE NOT NULL,
`PlayerID` INT UNSIGNED NOT NULL,
`ParentID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`PlayerID`, `ParentID`),
INDEX `fk_Registers_Parent1_idx` (`ParentID` ASC) VISIBLE,
CONSTRAINT `fk_Registers_Player1`
FOREIGN KEY (`PlayerID`)
REFERENCES `fmr19a`.`Player` (`PlayerID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Registers_Parent1`
FOREIGN KEY (`ParentID`)
REFERENCES `fmr19a`.`Parent` (`ParentID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = DEFAULT
and this is my code
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';
DROP SCHEMA IF EXISTS `fmr19a` ;
CREATE SCHEMA IF NOT EXISTS `fmr19a` DEFAULT CHARACTER SET utf8 ;
SHOW WARNINGS;
USE `fmr19a` ;
DROP TABLE IF EXISTS `fmr19a`.`Parent` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `fmr19a`.`Parent` (
`ParentID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Parent_fname` VARCHAR(45) NOT NULL,
`Parent_lname` VARCHAR(45) NOT NULL,
`Parent_street` VARCHAR(45) NOT NULL,
`Parent_city` VARCHAR(30) NOT NULL,
`Parent_state` CHAR(2) NOT NULL,
`Parent_zip` INT NOT NULL,
`Parent_phone` BIGINT NOT NULL,
PRIMARY KEY (`ParentID`))
ENGINE = InnoDB
AUTO_INCREMENT = 1;
SHOW WARNINGS;
DROP TABLE IF EXISTS `fmr19a`.`Team` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `fmr19a`.`Team` (
`TeamID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Team_name` VARCHAR(45) BINARY NOT NULL,
PRIMARY KEY (`TeamID`))
ENGINE = InnoDB
AUTO_INCREMENT = 2001;
SHOW WARNINGS;
DROP TABLE IF EXISTS `fmr19a`.`Player` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `fmr19a`.`Player` (
`PlayerID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Player_fname` VARCHAR(45) NOT NULL,
`Player_lname` VARCHAR(45) NOT NULL,
`Player_age` TINYINT NOT NULL,
`Player_phone` BIGINT NOT NULL,
`TeamID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`PlayerID`),
INDEX `fk_Player_Team1_idx` (`TeamID` ASC) VISIBLE,
CONSTRAINT `fk_Player_Team1`
FOREIGN KEY (`TeamID`)
REFERENCES `fmr19a`.`Team` (`TeamID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1001;
SHOW WARNINGS;
DROP TABLE IF EXISTS `fmr19a`.`Registers` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `fmr19a`.`Registers` (
`reg_cost` DECIMAL(5,2) NOT NULL,
`reg_date` DATE NOT NULL,
`PlayerID` INT UNSIGNED NOT NULL,
`ParentID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`PlayerID`, `ParentID`),
INDEX `fk_Registers_Parent1_idx` (`ParentID` ASC) VISIBLE,
CONSTRAINT `fk_Registers_Player1`
FOREIGN KEY (`PlayerID`)
REFERENCES `fmr19a`.`Player` (`PlayerID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `fk_Registers_Parent1`
FOREIGN KEY (`ParentID`)
REFERENCES `fmr19a`.`Parent` (`ParentID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = DEFAULT;
SHOW WARNINGS;
DROP TABLE IF EXISTS `fmr19a`.`Coach` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `fmr19a`.`Coach` (
`CoachID` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`Coach_fname` VARCHAR(45) NOT NULL,
`Coach_lname` VARCHAR(45) NOT NULL,
`Coach_phone` BIGINT NOT NULL,
`TeamID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`CoachID`),
INDEX `fk_Coach_Team1_idx` (`TeamID` ASC) VISIBLE,
CONSTRAINT `fk_Coach_Team1`
FOREIGN KEY (`TeamID`)
REFERENCES `fmr19a`.`Team` (`TeamID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 2031;
SHOW WARNINGS;
DROP TABLE IF EXISTS `fmr19a`.`Color` ;
SHOW WARNINGS;
CREATE TABLE IF NOT EXISTS `fmr19a`.`Color` (
`ColorID` INT UNSIGNED NOT NULL,
`Color_name` VARCHAR(25) NOT NULL,
`TeamID` INT UNSIGNED NOT NULL,
PRIMARY KEY (`ColorID`, `TeamID`),
INDEX `fk_Color_Team1_idx` (`TeamID` ASC) VISIBLE,
CONSTRAINT `fk_Color_Team1`
FOREIGN KEY (`TeamID`)
REFERENCES `fmr19a`.`Team` (`TeamID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 001;
SHOW WARNINGS;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
START TRANSACTION;
USE `fmr19a`;
INSERT INTO `fmr19a`.`Parent` (`ParentID`, `Parent_fname`, `Parent_lname`, `Parent_street`, `Parent_city`, `Parent_state`, `Parent_zip`, `Parent_phone`) VALUES (DEFAULT, 'Mary', 'Tanner', '101 Lane St.', 'Jacksonville', 'FL', 45445, 9048933748);
INSERT INTO `fmr19a`.`Parent` (`ParentID`, `Parent_fname`, `Parent_lname`, `Parent_street`, `Parent_city`, `Parent_state`, `Parent_zip`, `Parent_phone`) VALUES (DEFAULT, 'Tim', 'Brown', '1400 Lincoln Rd.', 'Jacksonville', 'FL', 45423, 9384929384);
INSERT INTO `fmr19a`.`Parent` (`ParentID`, `Parent_fname`, `Parent_lname`, `Parent_street`, `Parent_city`, `Parent_state`, `Parent_zip`, `Parent_phone`) VALUES (DEFAULT, 'Anna', 'Thomas', '2040 Sunshine Blvd.', 'St.Augustine', 'FL', 34323, 8358473948);
INSERT INTO `fmr19a`.`Parent` (`ParentID`, `Parent_fname`, `Parent_lname`, `Parent_street`, `Parent_city`, `Parent_state`, `Parent_zip`, `Parent_phone`) VALUES (DEFAULT, 'James', 'Jones', '4227 Lakeside Rd.', 'Augusta', 'GA', 45445, 9837474653);
INSERT INTO `fmr19a`.`Parent` (`ParentID`, `Parent_fname`, `Parent_lname`, `Parent_street`, `Parent_city`, `Parent_state`, `Parent_zip`, `Parent_phone`) VALUES (DEFAULT, 'Billy', 'Bob', '9772 Seminole Way', 'Jacsonville', 'FL', 34323, 93847382735);
COMMIT;
START TRANSACTION;
USE `fmr19a`;
INSERT INTO `fmr19a`.`Team` (`TeamID`, `Team_name`) VALUES (DEFAULT, 'Wildcats');
INSERT INTO `fmr19a`.`Team` (`TeamID`, `Team_name`) VALUES (DEFAULT, 'Seminoles');
INSERT INTO `fmr19a`.`Team` (`TeamID`, `Team_name`) VALUES (DEFAULT, 'Tigers');
INSERT INTO `fmr19a`.`Team` (`TeamID`, `Team_name`) VALUES (DEFAULT, 'Jaguars');
INSERT INTO `fmr19a`.`Team` (`TeamID`, `Team_name`) VALUES (DEFAULT, 'Bulldogs');
COMMIT;
START TRANSACTION;
USE `fmr19a`;
INSERT INTO `fmr19a`.`Player` (`PlayerID`, `Player_fname`, `Player_lname`, `Player_age`, `Player_phone`, `TeamID`) VALUES (DEFAULT, 'Rani', 'Tanner', 15, 9283748593, DEFAULT);
INSERT INTO `fmr19a`.`Player` (`PlayerID`, `Player_fname`, `Player_lname`, `Player_age`, `Player_phone`, `TeamID`) VALUES (DEFAULT, 'Jerry', 'Brown', 16, 9237482734, DEFAULT);
INSERT INTO `fmr19a`.`Player` (`PlayerID`, `Player_fname`, `Player_lname`, `Player_age`, `Player_phone`, `TeamID`) VALUES (DEFAULT, 'Adrian', 'Thomas', 15, 9237485733, DEFAULT);
INSERT INTO `fmr19a`.`Player` (`PlayerID`, `Player_fname`, `Player_lname`, `Player_age`, `Player_phone`, `TeamID`) VALUES (DEFAULT, 'Dana', 'Jones', 15, 9495682113, DEFAULT);
INSERT INTO `fmr19a`.`Player` (`PlayerID`, `Player_fname`, `Player_lname`, `Player_age`, `Player_phone`, `TeamID`) VALUES (DEFAULT, 'Jessica', 'Bob', 16, 8374628334, DEFAULT);
COMMIT;
START TRANSACTION;
USE `fmr19a`;
INSERT INTO `fmr19a`.`Registers` (`reg_cost`, `reg_date`, `PlayerID`, `ParentID`) VALUES (25.00, '2022-02-12', DEFAULT, DEFAULT);
INSERT INTO `fmr19a`.`Registers` (`reg_cost`, `reg_date`, `PlayerID`, `ParentID`) VALUES (20.00, '2022-03-02', DEFAULT, DEFAULT);
INSERT INTO `fmr19a`.`Registers` (`reg_cost`, `reg_date`, `PlayerID`, `ParentID`) VALUES (30.00, '2022-10-12', DEFAULT, DEFAULT);
INSERT INTO `fmr19a`.`Registers` (`reg_cost`, `reg_date`, `PlayerID`, `ParentID`) VALUES (25.00, '2022-04-07', DEFAULT, DEFAULT);
INSERT INTO `fmr19a`.`Registers` (`reg_cost`, `reg_date`, `PlayerID`, `ParentID`) VALUES (30.00, '2022-12-11', DEFAULT, DEFAULT);
COMMIT;
START TRANSACTION;
USE `fmr19a`;
INSERT INTO `fmr19a`.`Coach` (`CoachID`, `Coach_fname`, `Coach_lname`, `Coach_phone`, `TeamID`) VALUES (DEFAULT, 'Daniel', 'Adams', 948393743, DEFAULT);
INSERT INTO `fmr19a`.`Coach` (`CoachID`, `Coach_fname`, `Coach_lname`, `Coach_phone`, `TeamID`) VALUES (DEFAULT, 'Ryan', 'Smith', 8320239434, DEFAULT);
INSERT INTO `fmr19a`.`Coach` (`CoachID`, `Coach_fname`, `Coach_lname`, `Coach_phone`, `TeamID`) VALUES (DEFAULT, 'Martina', 'Jones', 9383827403, DEFAULT);
INSERT INTO `fmr19a`.`Coach` (`CoachID`, `Coach_fname`, `Coach_lname`, `Coach_phone`, `TeamID`) VALUES (DEFAULT, 'Camille', 'Gonzalez', 7384938475, DEFAULT);
INSERT INTO `fmr19a`.`Coach` (`CoachID`, `Coach_fname`, `Coach_lname`, `Coach_phone`, `TeamID`) VALUES (DEFAULT, 'Rogers', 'James', 3948473849, DEFAULT);
COMMIT;
START TRANSACTION;
USE `fmr19a`;
INSERT INTO `fmr19a`.`Color` (`ColorID`, `Color_name`, `TeamID`) VALUES (DEFAULT, 'Red', DEFAULT);
INSERT INTO `fmr19a`.`Color` (`ColorID`, `Color_name`, `TeamID`) VALUES (DEFAULT, 'Blue', DEFAULT);
INSERT INTO `fmr19a`.`Color` (`ColorID`, `Color_name`, `TeamID`) VALUES (DEFAULT, 'Green', DEFAULT);
INSERT INTO `fmr19a`.`Color` (`ColorID`, `Color_name`, `TeamID`) VALUES (DEFAULT, 'Orange', DEFAULT);
INSERT INTO `fmr19a`.`Color` (`ColorID`, `Color_name`, `TeamID`) VALUES (DEFAULT, 'White', DEFAULT);
COMMIT;
What I have tried:
I have tried changing the attributes or the primary keys but I honestly don't know when I misspelled or missed something.
Submit an article or tip