Link to home
Start Free TrialLog in
Avatar of Ritu Raj
Ritu Raj

asked on

Custom Auto Increment

I have a MySQL database. Can anyone help me on how to auto increment a column in which is an integer? I am totally new to databases

An increment is a 7 digit number and it has to custom auto increment

CREATE TABLE `march13airtelvd` (
  `id` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `country_id` int(50) NOT NULL,
  `operator_id` int(50) NOT NULL,
  `os` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `testType` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `placeType` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `performanceType` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `technologie` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `dateTest` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `testValue` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `lat` double NOT NULL,
  `lng` double NOT NULL,
  `nameCity` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `id4gmark` int(10) NOT NULL , (This column has to be in auto increment which starts from "1100000")
  `source` varchar(50) COLLATE utf8_unicode_ci NOT NULL

)
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image

You could set your column to auto-increment primary key and seed it to your starting value:

CREATE TABLE `march13airtelvd` (
    ....
    `id4gmark` int(10) NOT NULL PRIMARY KEY AUTO_INCREMENT
) AUTO_INCREMENT = 1100000;

Open in new window

JFYI, there is no need to have the autoincremented column set as a primary key.
Based on your table structure I would guess the ID column is used as a PK.
Avatar of Ritu Raj
Ritu Raj

ASKER

@Chris ... Thank you for your response. When I try to do the same as you suggest I get an error. I have attached the screenshot of the error for your reference.
sql-error.PNG
Post the command causing the error next time.

CREATE TABLE `march13airtelvd` (
    ....
    `id4gmark` int(10) NOT NULL AUTO_INCREMENT, 
    `source` varchar(50) COLLATE utf8_unicode_ci NOT NULL
) AUTO_INCREMENT = 1100000;

Open in new window

I'm guessing you've probably got a comma in the wrong place:

...
`nameCity` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`id4gmark` int(10) NOT NULL AUTO_INCREMENT,
`source` varchar(50) COLLATE utf8_unicode_ci NOT NULL
) AUTO_INCREMENT = 1100000;

Open in new window

note that such a col may contain gaps which may or may not be an issue
This question needs an answer!
Become an EE member today
7 DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.