How to resolve an error near NULL when creating a table-CodePudding

Whenever I try to create a table

CREATE TABLE registration` (`id` INT NOT NULL , `name` VARCHAR(30) NOT NULL , `email` VARCHAR(20) NOT NULL , `password` VARCHAR(15) NOT NULL , `DOB` DATE NOT NULL , `age` INT NOT NULL , `number` BIGINT NOT NULL , `religion` VARCHAR(10) NOT NULL , `education` VARCHAR(20) NOT NULL , `profession` VARCHAR(20) NOT NULL , `gender` ENUM NOT NULL , PRIMARY KEY (`id`)) ENGINE = InnoDB;

The following error occurs

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'NOT NULL , PRIMARY KEY (id)) ENGINE = InnoDB' at line 1

CodePudding user response：

ENUM needs values so you need to define the values they can get

CREATE TABLE registration (
    `id` INT NOT NULL 
    , `name` VARCHAR(30) NOT NULL 
    , `email` VARCHAR(20) NOT NULL 
    , `password` VARCHAR(15) NOT NULL 
    , `DOB` DATE NOT NULL 
    , `age` INT NOT NULL 
    , `number` BIGINT NOT NULL 
    , `religion` VARCHAR(10) NOT NULL 
    , `education` VARCHAR(20) NOT NULL 
    , `profession` VARCHAR(20) NOT NULL 
    , `gender` ENUM ('male','female')
 , PRIMARY KEY (`id`)
) ENGINE = InnoDB;

CodePudding user response：

When you declare a ENUM column (your gender column) you have to assign all possible values for the enumeration (See here for syntax)