Error trying to create table in MySQL via terminal

Question

I am trying to create a table in my database via terminal.

Here is my syntax:

CREATE TABLE `users` (
   PRIMARY KEY(id) NOT NULL AUTO_INCREMENT,
   `last_name` VARCHAR NOT NULL,
   `first_name` VARCHAR NOT NULL,
   `gender`  VARCHAR NOT NULL,
   `fav_color`  VARCHAR NOT NULL,
   `birthdate`  DATE NOT NULL
);

I am getting this error:

ERROR 1064 (42000): 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 AUTO_INCREMENT,
`last_name` VARCHAR NOT NULL,
`first_name` VARCHA' at line 2

What am i dong wrong here?

Answer 1

Besides the issues that Jens and Marc pointed out, You have to declare the length of your Varchar fields in order for this statement to work, like so:

CREATE TABLE `test`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `last_name` VARCHAR(45) NOT NULL,
  `first_name` VARCHAR(45) NOT NULL,
  `gender` VARCHAR(45) NOT NULL,
  `fav_color` VARCHAR(45) NOT NULL,
  `birthdate` DATE NOT NULL,
  PRIMARY KEY (`id`));

Answer 2

the syntax of your create statement is wrong:

the correct one is this:

CREATE TABLE `users` (
   `id` int NOT NULL AUTO_INCREMENT,
   `last_name` VARCHAR(255) NOT NULL,
   `first_name` VARCHAR(255) NOT NULL,
   `gender`  VARCHAR(255) NOT NULL,
   `fav_color`  VARCHAR(255) NOT NULL,
   `birthdate`  DATE NOT NULL,
   PRIMARY KEY(`id`)
);

For more Information see the offical documentation.

Answer 3

it should be

id int primary key auto_increment not null

You're trying to define a primary key on a field that doesn't exist. Keys cannot be "not null" and definitely cannot be "auto_increment".