CODEWITHSUNDEEP
mysqlprimary-key
user198729
user198729

Reputation: 63646

How to make a primary key start from 1000?

create table tablename (
    id integer unsigned not null AUTO_INCREMENT,
    ....
    primary key id
);

I need the primary key to start from 1000.

I'm using MySQL.

Upvotes: 53

Views: 102999

Answers (5)

Muneeb Ejaz
Muneeb Ejaz

Reputation: 884

little bit about your code NOT NULL is redundant as primary key specifies NOT NULL & UNIQUE on id.

answering your primary question for auto incrementing to start from 1000 execute the following SQL query.

CREATE TABLE tablename (
    id integer unsigned not null AUTO_INCREMENT,
    ....
    primary key (id)
) AUTO_INCREMENT=1000;

If you want to alter the existing table for id to start from 1000 then there are two ways you can achieve this.

ALTER TABLE tablename  MODIFY id int unsigned AUTO_INCREMENT primary key, AUTO_INCREMENT=3;

or

ALTER TABLE tablename AUTO_INCREMENT = 1000;

Upvotes: 0

davidosomething
davidosomething

Reputation: 3437

If your table has already been created with an auto-increment. so you can use 

ALTER TABLE tbl AUTO_INCREMENT = 1000;

otherwise put the AUTO_INCREMENT = 1000; in your CREATE TABLE

it goes before the final );

Upvotes: 101

Sarfraz
Sarfraz

Reputation: 382726

Well in such a situation, i simply open up my mysql client software and i type 1000, etc right in the primary key field. The record getting inserted will have ids greater than 1000 now.

Upvotes: -4

Chris C
Chris C

Reputation: 2013

ALTER TABLE yourtable AUTO_INCREMENT = 1000

Upvotes: 7

zombat
zombat

Reputation: 94167

You can use ALTER TABLE to accomplish this:

ALTER TABLE tablename AUTO_INCREMENT = 1000;

If you want it as part of the CREATE TABLE statement, just put it after the table definition:

CREATE TABLE tablename (
  ...
) ENGINE=InnoDB AUTO_INCREMENT=1000;

Upvotes: 44

Related Questions