CODEWITHSUNDEEP
mysqlsql
Jorge Figueroa yknx4
Jorge Figueroa yknx4

Reputation: 1

MySql Error when adding table

this is a sql query i'm trying to execute but it fails with this error:

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 'NOT NULL, passhash VARCHAR NOT NULL, permission TINYINT NOT NULL DEFAULT' at line 3:

And this is the query:

DROP TABLE IF EXISTS `Maestros`;

CREATE TABLE `Maestros` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR NOT NULL,
  `passhash` VARCHAR NOT NULL,
  `permission` TINYINT NOT NULL DEFAULT 0,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `activo` TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
KEY (`passhash`)
) COMMENT 'Tabla que contiene la informacion de los maestros';

Upvotes: 0

Views: 41

Answers (2)

Tarun Parswani
Tarun Parswani

Reputation: 4803

The problems in your MySQL query are as below: - Whenever you specify varchar as the datatype you need to have max size of the column defined i.e. varchar(50) instead of simply varchar [where 50 is the no of characters allowed in column] - You cannot have the 'DEFAULT CURRENT_TIMESTAMP' constraint used for more than one column in a single query

The modified query is as below...

DROP TABLE IF EXISTS `Maestros`;

CREATE TABLE `Maestros` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(100) NOT NULL,
  `passhash` VARCHAR(100) NOT NULL,
  `permission` TINYINT NOT NULL DEFAULT 0,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` TIMESTAMP NOT NULL,
  `activo` TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
 KEY (`passhash`)
) COMMENT 'Tabla que contiene la informacion de los maestros';

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269443

varchar should have a length associated with it and there can be only one TimeStamp with a default:

CREATE TABLE `Maestros` (
  `id` INTEGER NOT NULL AUTO_INCREMENT,
  `nombre` VARCHAR(255) NOT NULL,
  `passhash` VARCHAR(255) NOT NULL,
  `permission` TINYINT NOT NULL DEFAULT 0,
  `created` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `modified` TIMESTAMP,
  `activo` TINYINT NOT NULL DEFAULT 1,
  PRIMARY KEY (`id`),
KEY (`passhash`)
) COMMENT 'Tabla que contiene la informacion de los maestros';

Here is a SQL Fiddle.

Upvotes: 1

Related Questions