CODEWITHSUNDEEP
mysql
Arunkumar
Arunkumar

Reputation: 31

How to insert data to MySQL by passing value '' for a auto incremented column?

CREATE TABLE IF NOT EXISTS `myproducts` (
              `uid` int(10) unsigned NOT NULL AUTO_INCREMENT,
              `parent_id` int(10) unsigned DEFAULT NULL,
              `type_id` varchar(64) COLLATE utf8_czech_ci NOT NULL DEFAULT 'NormalPage',
              `name` varchar(255) COLLATE utf8_czech_ci NOT NULL DEFAULT '',
              PRIMARY KEY (`uid`),
              KEY `index_2` (`type_id`,`t`),
              KEY `name` (`name`),
              KEY `parent_id` (`parent_id`,`name`(48))
            ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_czech_ci ROW_FORMAT=DYNAMIC AUTO_INCREMENT=189 ;

This is my insert statement.

INSERT INTO `mydb`.`myproducts` (`uid`, `parent_id`, `type_id`, `name`) VALUES ('',20, 'Product Category', 'WaterBottels');

Problem Description : When i use this insert query in my system which is having mysql version 5.5.16 it is perfectly inserting the record but when i try this in another system which is also having mysql version 5.5.16 it is giving problem.

I do know we should pass NULL or '0' for the auto increment column , but my question why it is working for my instance.

As it is working for my instance , i am considering we can pass '' as the value for auto increment column. if so to work it out in other system do i need to change any mysql configurations.

Upvotes: 1

Views: 3401

Answers (5)

user2936213
user2936213

Reputation: 1011

Since your column uid id auto incremented, you don't need to pass a value while insertion. Try this: 

INSERT INTO `mydb`.`myproducts` (`parent_id`, `type_id`, `name`) VALUES (20, 'Product Category', 'WaterBottels');

This will work on all systems and your uid will get auto incremented.

Upvotes: 0

Krish R
Krish R

Reputation: 22711

Since You are using uid as a AUTO_INCREMENT.

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.

By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

You can use 

INSERT INTO `mydb`.`myproducts` (`parent_id`, `type_id`, `name`) VALUES (20, 'Product Category', 'WaterBottels');

instead of 

  INSERT INTO `mydb`.`myproducts` (`uid`, `parent_id`, `type_id`, `name`) VALUES ('',20, 'Product Category', 'WaterBottels');

Upvotes: 0

Fathah Rehman P
Fathah Rehman P

Reputation: 8741

user either of the following

INSERT INTO `mydb`.`myproducts` (`uid`, `parent_id`, `type_id`, `name`) VALUES
 (null,20, 'Product Category', 'WaterBottels');

OR

  INSERT INTO `mydb`.`myproducts` ( `parent_id`, `type_id`, `name`) VALUES
     (20, 'Product Category', 'WaterBottels');

Upvotes: 0

HengChin
HengChin

Reputation: 593

Values is not required for AUTO_INCREMENT fields as it will be auto generated. so you can insert a row without specifying the value for column uid.

Upvotes: 0

Anil
Anil

Reputation: 2554

Try executing this query to insert:

INSERT INTO `mydb`.`myproducts` (`parent_id`, `type_id`, `name`) 
VALUES (20, 'Product Category', 'WaterBottels');

You don't need to specify the primary key column as that will be auto inserted.

Upvotes: 0

Related Questions