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.
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','[email protected]');
insert employee (EmployeeNumber,FirstName,LastName,Department,Phone,Email) values (222,'Donald','','Engineering','1','[email protected]');
insert employee (EmployeeNumber,FirstName,LastName,Phone,Email) values (223,'Kris','','2','[email protected]');
insert employee (EmployeeNumber,FirstName,LastName,Phone,Email) values (2277,'Kim','','3','[email protected]');
select * from employee;
insert employee (FirstName,LastName,Phone,Email) values ('Auto','','44','[email protected]'); -- 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','[email protected]');
insert employee (FirstName,LastName,Phone,Email) values ('Kris','','2','[email protected]');
insert employee (FirstName,LastName,Phone,Email) values ('Kim','','3','[email protected]');
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','[email protected]');
select * from employee;
select * from project;