CODEWITHSUNDEEP
mysqlheidisql
Anjali
Anjali

Reputation: 2698

inserting autoincrement and current date time in a table

I am using heidiSQL to create a table. I have two columns in that table where I want AutoIncrement and current DateTime. When I selected the default value of autoIncrement and now for those two columns. I get an error. below is the screen shot of the error:

enter image description here

below is the screen shot of what I did to put now and autoIncrement:

enter image description here

This is the query generated by HeidiSQL:

CREATE TABLE `personalidentity` (
    `VitalID` INT(10,0) NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `MiddleName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `LastName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `Address` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `City` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `State` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `Zip` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `UpdatedOn` DATETIME NOT NULL DEFAULT 'Now()',
    `StatusCode` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

I am very new to mysql. Any help in resolving the error will be highly appreciated.

Upvotes: 0

Views: 1592

Answers (2)

nbk
nbk

Reputation: 49373

Integer has no second number to signal the size In mysql 8 you can skip that all together and wrote INTEGER.

VitalID has to be PRIMARY KEY or at least KEY and NOT NULL

'NOW()' is a string and can' be used as DEFAULT get rid of the ticks

It doesn't matter Integer has always the same size

CREATE TABLE `personalidentity` (
    `VitalID` INT(10) PRIMARY KEY NOT  NULL AUTO_INCREMENT,
    `FirstName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `MiddleName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `LastName` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `Address` VARCHAR(200) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `City` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `State` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `Zip` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci',
    `UpdatedOn` DATETIME NOT NULL DEFAULT Now(),
    `StatusCode` VARCHAR(10) NULL DEFAULT NULL COLLATE 'utf8mb4_0900_ai_ci'
)
COLLATE='utf8mb4_0900_ai_ci'
ENGINE=InnoDB
;

Upvotes: 1

Shoaeb
Shoaeb

Reputation: 919

and you had two other errors which i solved..

1.No Primary Key

2.Collation

3.Default Now() Value.

CREATE TABLE `personalidentity` (
    `VitalID` INT(10) primary key auto_increment ,
    `FirstName` VARCHAR(50) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `MiddleName` VARCHAR(50) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `LastName` VARCHAR(50) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `Address` VARCHAR(200) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `City` VARCHAR(50) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `State` VARCHAR(50) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `Zip` VARCHAR(50) NULL DEFAULT NULL COLLATE utf8mb4_general_ci ,
    `UpdatedOn` DATETIME NOT NULL DEFAULT now(),
    `StatusCode` VARCHAR(10) NULL DEFAULT NULL COLLATE utf8mb4_general_ci 
)
COLLATE=utf8mb4_general_ci 
ENGINE=InnoDB
;

Upvotes: 2

Related Questions