CODEWITHSUNDEEP
mysqldatabasefieldprimary-key
Dev'Dev
Dev'Dev

Reputation: 265

How to create INT field (not a primary key) who start at 1000 with auto_increment

I want to create a field INT(11) in my MYSQL Database who start at the value of 1000 and is incremented of 1 after each INSERT.

WARNING: THIS IS NOT A PRIMARY KEY

The DB is running with MYSQL 6.0 and InnoDB engine

Who can I achieve this, if it's possible ?

Upvotes: 3

Views: 439

Answers (2)

Abhishek
Abhishek

Reputation: 1561

Try below code hope this should help you focus on the syntax part in below code -- KEY (id) --

CREATE TABLE IF NOT EXISTS `users`(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(100) NOT NULL,
  `email` varchar(80) NOT NULL,
  `type` int(11) NOT NULL,
  `created` datetime NOT NULL,
  `modified` datetime NOT NULL,
  `status` enum('1','0') NOT NULL COMMENT '''0'' for inactive ''1'' for active',
   PRIMARY KEY (`username`),
   KEY (id)    
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1000;

Upvotes: 0

Clart Tent
Clart Tent

Reputation: 1309

You can have an auto_increment column as long as it is defined as a key (it doesn't have to be a PRIMARY KEY). So for example:

CREATE TABLE auto_inc_test (
    ID INT PRIMARY KEY,
    IncValue INT AUTO_INCREMENT,
    SomeData VARCHAR(50),
    INDEX(IncValue)
);
ALTER TABLE auto_inc_test AUTO_INCREMENT = 1000;

(The ALTER TABLE line sets the next value for the AUTO_INCREMENT.)

If you then run the following insert (which, obviously, gives no value for the IncValue field):

INSERT INTO auto_inc_test (ID, SomeData) 
VALUES (1, 'test 1'), (2, 'test 2'), (3, 'test3')

You'll get:

ID    IncValue    SomeData
1     1000        test 1
2     1001        test 2
3     1002        test 3

Upvotes: 1

Related Questions