How to set AUTO_INCREMENT default value and rate of increase in MySQL 5.6

Question

I want 'ProjectID' to start at 1000 and increment by 100.

How do I set an AUTO_INCREMENT default value and rate in MySQL 5.6? In other words, if I want to create a primary key that starts at 1000 and increases by 100, how do I do that in MySQL?

CREATE TABLE IF NOT EXISTS PROJECT(  
ProjectID Int(4) AUTO_INCREMENT PRIMARY KEY,  
ProjectName Char(20) NOT NULL,  
DepartmentName Char(30) NOT NULL,  
MaxHours Int(14) NOT NULL,  
StartDate Char(10) NOT NULL,  
EndDate Char(10) NULL)  
ENGINE=InnoDB AUTO_INCREMENT=1000;

I want 'ProjectID' to start at 1000 and increment by 100.

possible duplicate of [Change auto increment starting number?](http://stackoverflow.com/questions/970597/change-auto-increment-starting-number) — Norbert, Jul 09 '15 at 01:30
You are trying to create gaps cuz you like the concept but are too new to appreciate its simplicity...and want to wedge in data later? — Drew, Jul 09 '15 at 01:52
@DrewPierce Actually I didn't think I had to enter the data. I figured using modern English instead of SQL might tickle a neuron. — Donald Moore, Jul 09 '15 at 02:21
Long live google and mysql manuals: https://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_offset — Norbert, Jul 09 '15 at 02:23
@NorbertvanNobelen Sorry, I'm using MySQL 5.6. AUTO_INCREMENT_INCREMENT is not a valid command in 5.6. I shall update the question again. — Donald Moore, Jul 09 '15 at 02:28
THis was already present in 5.0, so 5.6 should work. Are you using innodb because that is required for this to work. — Norbert, Jul 09 '15 at 02:29
Did you already know that was a command or did you just search auto_increment commands? Thanks, @NorbertvanNobelen ! — Donald Moore, Jul 09 '15 at 02:33

Drew · Accepted Answer · 2015-07-12T01:00:19.953

Start with empty table.

ALTER TABLE tblName AUTO_INCREMENT = 1000;

Perform your insert to this special 100 gap table in one and only one place.

Let's call that place a stored proc (not mandatory).

Commit to the discipline of that approach.

In that stored proc lock the table, do an insert.

DECLARE a query as a "string" and then execute that string via a Prepared Statement. That string is like the above Alter Table but with auto_increment=xxxx

Where xxxx=max(ProjectID)+100

Unlock table. Exit stored proc.

The reason is that Alter Table tblName auto_increment = variable_name will barf. So it needs to be an executed Prepared Statement.

Edit as promised:

drop schema wpc;
CREATE SCHEMA IF NOT EXISTS WPC;

use wpc;

CREATE TABLE IF NOT EXISTS department 
( 
    Department varchar(30) NOT NULL,    -- chg 
    BudgetCode int(20) NOT NULL, 
    OfficeNumber int(10) NOT NULL, 
    Phone varchar(12) DEFAULT NULL, -- chg 
    PRIMARY KEY (Department) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS employee 
( 
    EmployeeNumber int(4) AUTO_INCREMENT, 
    FirstName varchar(25) NOT NULL, -- chg
    LastName varchar(25) NOT NULL, -- chg
    Department varchar(30) NOT NULL DEFAULT 'Human Resources', 
    Phone varchar(17) DEFAULT NULL, -- chg 
    Email varchar(100) NOT NULL, 

    PRIMARY KEY (EmployeeNumber), 
    UNIQUE KEY Email (Email), 
    KEY DepartmentFK (Department), 

    CONSTRAINT DepartmentFK 
    FOREIGN KEY (Department) 
    REFERENCES department (Department) ON UPDATE CASCADE 
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE IF NOT EXISTS project 
( 
    ProjectID INT(4) NOT NULL AUTO_INCREMENT, 
    ProjectName VARCHAR(30) NOT NULL, -- chg
    Department VARCHAR(30) NOT NULL,  -- chg
    MaxHours INT(3) NOT NULL DEFAULT '100', 
    StartDate datetime NOT NULL, -- chg
    EndDate datetime DEFAULT NULL, -- chg

    PRIMARY KEY (ProjectID), 
    KEY ProjectFK (Department), 

    CONSTRAINT ProjectFK 
    FOREIGN KEY (Department) 
    REFERENCES department (Department) ON UPDATE CASCADE
) ENGINE=INNODB DEFAULT CHARSET=LATIN1 AUTO_INCREMENT=1000;

CREATE TABLE IF NOT EXISTS assignment 
( 
    ProjectID INT(4) NOT NULL AUTO_INCREMENT, 
    EmployeeNumber INT(4) NOT NULL, 
    HoursWorked INT(4) NOT NULL, 

    PRIMARY KEY (ProjectID), 
    -- UNIQUE KEY EmployeeNumber (EmployeeNumber), -- kill this dupe, plus it won't be unique 
    -- KEY ProjectFK1 (ProjectID), -- don't have this it is already a PK
    KEY EmployeeFK1 (EmployeeNumber), -- keep this as it won't be unique
    -- duplicate and unnecessary keys just slow down system. you had 4. you need 2

    CONSTRAINT EmployeeFK1 
    FOREIGN KEY (EmployeeNumber) 
    REFERENCES employee (EmployeeNumber), 

    CONSTRAINT ProjectFK1 
    FOREIGN KEY (ProjectID) 
    REFERENCES project (ProjectID) ON DELETE CASCADE 
) ENGINE=INNODB DEFAULT CHARSET=LATIN1;

show table status like '%'; -- auto_incs look good

insert project (ProjectName,Department,MaxHours,StartDate,EndDate) values ('Dismantle Kryptonite','Engineering',1000,'2015-04-01',null);
-- fk error, good, do dept first. BUT NOTE this failure screws up auto_inc so next insert is 1001 yikes
-- so re-do, drop schema, create schema, skip failed first insert above and start below:
insert department (Department,BudgetCode,OfficeNumber,Phone) values ('Engineering',111,222,null);
insert department (Department,BudgetCode,OfficeNumber,Phone) values ('Human Resources',107,223,null);
select * from department;
-- all looks well
insert project (ProjectName,Department,MaxHours,StartDate,EndDate) values ('Dismantle Kryptonite','Engineering',1000,'2015-04-01',null);
select * from project; -- projectId 1000
alter table project auto_increment=1010;
insert project (ProjectName,Department,MaxHours,StartDate,EndDate) values ('Fire old employees','Human Resources',2,'2015-04-02',null);
alter table project auto_increment=1020;
insert project (ProjectName,Department,MaxHours,StartDate,EndDate) values ('Regret, Hire back','Human Resources',2,'2015-04-02',null);
alter table project auto_increment=1030;

select * from project;

fk's look good and as expected. Try employee:

insert employee (EmployeeNumber,FirstName,LastName,Department,Phone,Email) values (222,'Donald','','bad-dept','1','d@g.com'); 
insert employee (EmployeeNumber,FirstName,LastName,Department,Phone,Email) values (222,'Donald','','Engineering','1','d@g.com'); 
insert employee (EmployeeNumber,FirstName,LastName,Phone,Email) values (223,'Kris','','2','k@g.com'); 
insert employee (EmployeeNumber,FirstName,LastName,Phone,Email) values (2277,'Kim','','3','kim@g.com'); 
select * from employee;
insert employee (FirstName,LastName,Phone,Email) values ('Auto','','44','auto1@g.com'); -- 2278
do a re-do of everything top to bottom but skipping employee inserts except the below to run:

insert employee (FirstName,LastName,Department,Phone,Email) values ('Donald','','Engineering','1','d@g.com'); 
insert employee (FirstName,LastName,Phone,Email) values ('Kris','','2','k@g.com'); 
insert employee (FirstName,LastName,Phone,Email) values ('Kim','','3','kim@g.com'); 
select * from employee;
insert project (ProjectName,Department,MaxHours,StartDate,EndDate) values ('Hire Joe','Human Resources',2,'2015-05-02',null);
alter table project auto_increment=1040;
insert employee (FirstName,LastName,Phone,Email) values ('Jason','','66','jj@g.com'); 
select * from employee;
select * from project;

@@auto_increment_increment sets the increment value for the entire schema. I can't have that because another table has to increment by 1. Some of this stuff is over my head because I have no experience using stored procedures. I have created a new question and provided the data in it's entirety at http://stackoverflow.com/questions/31362069/how-to-set-auto-increment-increment-on-a-specific-table-in-mysql — Donald Moore, Jul 11 '15 at 21:35
As i described it auto_increment is at the table level and the only way i would do it. Ask around. Same answer — Drew, Jul 11 '15 at 22:11
Auto_inc at the table-level as shown in line 2 of my answer,,, say you set to 2700 means next one is 2700 for that table only. If you don't reset it prior to the next one, the next one is 2701, then 2702. Play with it as it becomes obvious in 3 minutes max — Drew, Jul 11 '15 at 22:15
All I know is I just started learning about triggers and stored procedures. I know a little bit of C++ so I'm familiar with 'string'. Is that considered a datatype in SQL? I know how to create triggers but I have little knowledge beyond knowing the term 'stored procedure'. — Donald Moore, Jul 11 '15 at 22:46
Let me start to finish show you what I am talking about with your schema. Will take 30 minutes k? — Drew, Jul 11 '15 at 22:53
This does work. I have to set ProjectID and EmployeeNumber as a PK and FK because that's part of my job. Nevertheless, this is a solid way of creating the desired response and for that; we thank you. — Donald Moore, Jul 13 '15 at 23:54

score 0 · Answer 2 · answered Jul 10 '15 at 00:31

0

Create table:

CREATE TABLE IF NOT EXISTS PROJECT( ProjectID Int(4) AUTO_INCREMENT PRIMARY KEY, ProjectName Char(20) NOT NULL, DepartmentName Char(30) NOT NULL, MaxHours Int(14) NOT NULL, StartDate Char(10) NOT NULL, EndDate Char(10) NULL) ENGINE=InnoDB AUTO_INCREMENT=1000;

Then type:

SET @@AUTO_INCREMENT_INCREMENT=100;

Now ProjectID is a surrogate key that starts at 1000 and increases by 100.

https://dev.mysql.com/doc/refman/5.0/en/replication-options-master.html#sysvar_auto_increment_offset

Thank you @NorbertvanNobelen !

answered Jul 10 '15 at 00:31

Donald Moore

17
9

I thought this worked but @@AUTO_INCREMENT_INCREMENT ended up setting the rate of increment for the entire schema and not just that one table. I have recreated this question at http://stackoverflow.com/questions/31362069/how-to-set-auto-increment-increment-on-a-specific-table-in-mysql – Donald Moore Jul 11 '15 at 21:36

How to set AUTO_INCREMENT default value and rate of increase in MySQL 5.6

2 Answers2