Search code examples
mysqlheidisql

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.

Solution

  • 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
;