0

I'm getting error 1054 - unkown column in field list when trying to create a MySQL database and insert some new data. I'm using MySQL Workbench to foward engineer from a .mwb file. My create table looks like this

CREATE TABLE IF NOT EXISTS `JarvisDB`.`jarvisFixtures` (
  `Id` INT NOT NULL,
  `Gender` VARCHAR(10) NOT NULL,
  `Section` VARCHAR(10) NOT NULL,
  `Team1` VARCHAR(10) NOT NULL,
  `Team2` VARCHAR(10) NOT NULL,
  `FixtureDate` VARCHAR(45) NOT NULL,
  `FixtureTime` VARCHAR(10) NOT NULL,
  `Venue` VARCHAR(20) NOT NULL,
  `Court` VARCHAR(5) NOT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE INDEX `Id_UNIQUE` (`Id` ASC));

while insert looks like this

INSERT INTO `JarvisDB`.`jarvisFixtures` (`Id`, `Gender`, `Section`, `Team1`, `Team2`, `FixtureDate`, `FixtureTime`, `Venue`, `Court`) VALUES (1, 'Men', 'A', 'NW', 'Gauteng', '15 July 2015', '09:00', 'Potch CC', '3');

The error is complaining about column 'FixtureDate', which doesn't make any sense to me? Reading through other solutions on the web it seems like it may have something to do with if the column name is surrounded by backticks or single qoutes. But since all my columns are of type varchar (exluding the PK) I don't see why it should only start complaining when it reaches this particular column.

Can anyone shed some light on the issue?

Update

Full code now looks like this- 

-- 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='TRADITIONAL,ALLOW_INVALID_DATES';

-- -----------------------------------------------------
-- Schema JarvisDB
-- -----------------------------------------------------

-- -----------------------------------------------------
-- Schema JarvisDB
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `JarvisDB` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `JarvisDB` ;

-- -----------------------------------------------------
-- Table `JarvisDB`.`jarvisFixtures`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `JarvisDB`.`jarvisFixtures` (
  Id INT NOT NULL,
  Gender VARCHAR(10) NOT NULL,
  Section VARCHAR(10) NOT NULL,
  Team1 VARCHAR(10) NOT NULL,
  Team2 VARCHAR(10) NOT NULL,
  FixtureD DATETIME NOT NULL,
  FixtureT VARCHAR(10) NOT NULL,
  Venue VARCHAR(20) NOT NULL,
  Court VARCHAR(5) NOT NULL,
  PRIMARY KEY (Id),
  UNIQUE INDEX `Id_UNIQUE` (Id ASC))
ENGINE = InnoDB;


SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

-- -----------------------------------------------------
-- Data for table `JarvisDB`.`jarvisFixtures`
-- -----------------------------------------------------
START TRANSACTION;
USE `JarvisDB`;
INSERT INTO `JarvisDB`.`jarvisFixtures` (Id, Gender, Section, Team1, Team2, FixtureD, FixtureT, Venue, Court) VALUES (1, 'Men', 'A', 'NW', 'Gauteng', '15 July 2015', '09:00', 'Potch CC', '3');
INSERT INTO `JarvisDB`.`jarvisFixtures` (Id, Gender, Section, Team1, Team2, FixtureD, FixtureT, Venue, Court) VALUES (2, 'Men', 'A', 'NW', 'WP', '15 July 2015', '14:00', 'Potch CC', '2');
INSERT INTO `JarvisDB`.`jarvisFixtures` (Id, Gender, Section, Team1, Team2, FixtureD, FixtureT, Venue, Court) VALUES (3, 'Men', 'A', 'Northerns', 'Gauteng', '15 July 2015', '09:00', 'Potch CC', '1');
INSERT INTO `JarvisDB`.`jarvisFixtures` (Id, Gender, Section, Team1, Team2, FixtureD, FixtureT, Venue, Court) VALUES (4, 'Men', 'A', 'KZN', 'WP', '16 July 2015', '09:00', 'Defence', '5');
INSERT INTO `JarvisDB`.`jarvisFixtures` (Id, Gender, Section, Team1, Team2, FixtureD, FixtureT, Venue, Court) VALUES (5, 'Men', 'A', 'EP', 'NW', '16 July 2015', '12:00', 'Defence', '4');
INSERT INTO `JarvisDB`.`jarvisFixtures` (Id, Gender, Section, Team1, Team2, FixtureD, FixtureT, Venue, Court) VALUES (6, 'Men', 'A', 'WP', 'Northerns', '16 July 2015', '14:00', 'Puk', '5');

COMMIT;

Still getting the same error

Jonnny
  • 4,939
  • 11
  • 63
  • 93
Code Vader
  • 739
  • 3
  • 9
  • 26
  • 2
    What is the exact error message? – Barmar May 22 '15 at 06:15
  • See http://stackoverflow.com/questions/11321491/when-to-use-single-quotes-double-quotes-and-backticks – Barmar May 22 '15 at 06:15
  • 1
    I don't get an error at sqlfiddle: http://www.sqlfiddle.com/#!9/01e499 – Barmar May 22 '15 at 06:16
  • 4
    As an aside, wouldn't it be much better to have the date and time in a proper date and time field? – Pekka May 22 '15 at 06:19
  • It's probably just a typo. The data type of the column has no impact on whether the column name is known. – Barmar May 22 '15 at 06:22
  • I just tried running `create database JarvisDB;` and then your `CREATE TABLE` and `INSERT` statements on my local MySQL installation and they ran without error or warning. There must be something else going on here that you are not showing us. – Asaph May 22 '15 at 06:26
  • I've set the relevenat field types to DateTime. MySQL WorkBench also includes the following lines of code before the Table Create 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,ALLOW_INVALID_DATES'; and before the inserts SET SQL_MODE=@OLD_SQL_MODE; SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS; SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS; Do they have an impact? – Code Vader May 22 '15 at 06:35
  • @CodeVader Now that you've set the relevant field types, please post your updated `CREATE TABLE` statement. – Asaph May 22 '15 at 06:40

1 Answers1

0

Removed quotes in insert command this will work file. Working fine on MySQl 5.6

CREATE TABLE jarvisFixtures ( Id INT , Gender VARCHAR(10) , Section VARCHAR(10), Team1 VARCHAR(10) , Team2 VARCHAR(10) , FixtureDate VARCHAR(45), FixtureTime VARCHAR(10), Venue VARCHAR(20), Court VARCHAR(5), PRIMARY KEY (Id), UNIQUE INDEX Id_UNIQUE (Id ASC));

INSERT INTO jarvisFixtures (Id, Gender, Section, Team1, Team2, FixtureDate, FixtureTime, Venue, Court) VALUES (1, 'Men', 'A', 'NW', 'Gauteng', '15 July 2015', '09:00', 'Potch CC', '3')

piyush035
  • 11
  • 1