MySQL syntax check

  • mindfullsilence
  • Professor
  • Professor
  • User avatar
  • Posts: 854

Post 3+ Months Ago

I don't know squat about sql. So I tried this program. I exported to mysql query, and I'm getting errors when running in phpMyAdmin. Anyone know what the errors are?

SQL Code: [ Select ]
 
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='TRADITIONAL';
 
CREATE SCHEMA IF NOT EXISTS `stocks` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `stocks`;
 
-- -----------------------------------------------------
-- Table `stocks`.`symbols`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `stocks`.`symbols` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Symbol` VARCHAR(45) NOT NULL ,
  `CompanyName` VARCHAR(45) NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX () ,
  CONSTRAINT ``
    FOREIGN KEY ()
    REFERENCES `` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
   );
 
 
-- -----------------------------------------------------
-- Table `stocks`.`stockHistory`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `stocks`.`stockHistory` (
  `ID` INT NOT NULL AUTO_INCREMENT ,
  `Date` DATE NOT NULL ,
  `Open` DECIMAL(10,2) NOT NULL ,
  `High` DECIMAL(10,2) NOT NULL ,
  `Low` DECIMAL(10,2) NOT NULL ,
  `Close` DECIMAL(10,2) NOT NULL ,
  `Volume` BIGINT NOT NULL ,
  `sym_id` INT NOT NULL ,
  PRIMARY KEY (`ID`) ,
  INDEX `ID` (`sym_id` ASC) ,
  CONSTRAINT `ID`
    FOREIGN KEY (`sym_id` )
    REFERENCES `stocks`.`symbols` (`ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_unicode_ci;
 
 
 
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
 
  1.  
  2. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
  3. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
  4. SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';
  5.  
  6. CREATE SCHEMA IF NOT EXISTS `stocks` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
  7. USE `stocks`;
  8.  
  9. -- -----------------------------------------------------
  10. -- Table `stocks`.`symbols`
  11. -- -----------------------------------------------------
  12. CREATE  TABLE IF NOT EXISTS `stocks`.`symbols` (
  13.   `ID` INT NOT NULL AUTO_INCREMENT ,
  14.   `Symbol` VARCHAR(45) NOT NULL ,
  15.   `CompanyName` VARCHAR(45) NOT NULL ,
  16.   PRIMARY KEY (`ID`) ,
  17.   INDEX () ,
  18.   CONSTRAINT ``
  19.     FOREIGN KEY ()
  20.     REFERENCES `` ()
  21.     ON DELETE NO ACTION
  22.     ON UPDATE NO ACTION
  23.    );
  24.  
  25.  
  26. -- -----------------------------------------------------
  27. -- Table `stocks`.`stockHistory`
  28. -- -----------------------------------------------------
  29. CREATE  TABLE IF NOT EXISTS `stocks`.`stockHistory` (
  30.   `ID` INT NOT NULL AUTO_INCREMENT ,
  31.   `Date` DATE NOT NULL ,
  32.   `Open` DECIMAL(10,2) NOT NULL ,
  33.   `High` DECIMAL(10,2) NOT NULL ,
  34.   `Low` DECIMAL(10,2) NOT NULL ,
  35.   `Close` DECIMAL(10,2) NOT NULL ,
  36.   `Volume` BIGINT NOT NULL ,
  37.   `sym_id` INT NOT NULL ,
  38.   PRIMARY KEY (`ID`) ,
  39.   INDEX `ID` (`sym_id` ASC) ,
  40.   CONSTRAINT `ID`
  41.     FOREIGN KEY (`sym_id` )
  42.     REFERENCES `stocks`.`symbols` (`ID` )
  43.     ON DELETE NO ACTION
  44.     ON UPDATE NO ACTION)
  45. ENGINE = MyISAM
  46. DEFAULT CHARACTER SET = utf8
  47. COLLATE = utf8_unicode_ci;
  48.  
  49.  
  50.  
  51. SET SQL_MODE=@OLD_SQL_MODE;
  52. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
  53. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
  54.  


Errors start in the sql block just under the comment "-- Table `stocks`.`symbols`"
  • joebert
  • Fart Bubbles
  • Genius
  • User avatar
  • Posts: 13504
  • Loc: Florida

Post 3+ Months Ago

I'd remove the following stuff. It looks like the application you used includes possible lines in output even if they don't contain valid values.

For instance, I believe INDEX() should have a column name in the (), the CONSTRAINT, FOREIGN KEY, and REFERENCES should probably have arguments too. ON DELETE and ON UPDATE don't need to be there unless there is some sort of action taking place, as the default values for them are going to be, NO ACTION.

SQL Code: [ Select ]
  INDEX () ,
  CONSTRAINT ``
    FOREIGN KEY ()
    REFERENCES `` ()
    ON DELETE NO ACTION
    ON UPDATE NO ACTION
  1.   INDEX () ,
  2.   CONSTRAINT ``
  3.     FOREIGN KEY ()
  4.     REFERENCES `` ()
  5.     ON DELETE NO ACTION
  6.     ON UPDATE NO ACTION

Post Information

  • Total Posts in this topic: 2 posts
  • Users browsing this forum: No registered users and 64 guests
  • You cannot post new topics in this forum
  • You cannot reply to topics in this forum
  • You cannot edit your posts in this forum
  • You cannot delete your posts in this forum
  • You cannot post attachments in this forum
 
cron
 

© 1998-2014. Ozzu® is a registered trademark of Unmelted, LLC.