How to make second column increament if one column is already auto increament?

Question

I have three column(ID,Receipt_no,Name) in my table.ID is primary key auto increment so ID will start from 1, Same think i have to set on receipt_no so it will also start from 1.Is it possible?

I am using phpmyadmin.

Thanks in advance.

-- Table structure for table `temp`
--

CREATE TABLE IF NOT EXISTS `temp` (
`Id` int(11) NOT NULL,
  `Receipt_no` int(11) NOT NULL,
  `Name` varchar(20) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


ALTER TABLE `temp`
 ADD PRIMARY KEY (`Id`), ADD UNIQUE KEY `Receipt_no` (`Receipt_no`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `temp`
--
ALTER TABLE `temp`
MODIFY `Id` int(11) NOT NULL AUTO_INCREMENT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

Sure. Use my sequence number thingie [Here](http://stackoverflow.com/a/38079598) and fetch your next num for that non-`AUTO_INCREMENT` column. You have it, use it on the insert for the non AI column. Is it easy? No. No one said programming was always easy. — Drew, Sep 25 '16 at 03:55
Means i have to set receipt_no unique key?can you help me with simple query? — , Sep 25 '16 at 04:03
Do you want me to show you a full working demo with one auto_inc and one other incrementing number (let's say it starts at 1000)? But only if you can write stored procs (save them I mean) via PHPMyAdmin. — Drew, Sep 25 '16 at 04:08
Mr.Drew, I added table structure..If possible please share demo. — , Sep 25 '16 at 04:17
You may be able to set up a trigger to do so. Check out http://stackoverflow.com/questions/15388098/auto-increment-second-column for ideas. — Tim Dearborn, Sep 25 '16 at 04:18
@TimDearborn that is a custom prefix concat to match the autoinc id — Drew, Sep 25 '16 at 04:28
Sorry Mr.Tim i tried that code but i am getting zero value in receipt no. — , Sep 25 '16 at 04:42
If you want the `receipt_no` to equal `id` always, then don't even have a `receipt_no`. — Drew, Sep 25 '16 at 04:55
You can specify the first value for auto increment, so you don't have to start at 1. — Arjan, Oct 03 '16 at 19:15
Mr.Arjan, I have one primary key auto increment and i have to set same on second column. — , Oct 04 '16 at 16:48

score 1 · Answer 1 · edited May 23 '17 at 12:33

Create a db for safe testing:

create schema Hybreeder;
use Hybreeder;

Schema:

CREATE TABLE `temp` (
    `Id` int(11) AUTO_INCREMENT PRIMARY KEY,
    `Receipt_no` int(11) NOT NULL,
    `Name` varchar(20) NOT NULL,
    UNIQUE KEY `unq_Receipt_no` (`Receipt_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- The following table would be useful system wide for all
-- your special incrementor needs whatever they may be
-- Especially great for those looking for Prefixes to
-- add to PK's like an id such as ABC00001
create table sequences
(   id int auto_increment primary key,
    sectionType varchar(200) not null,
    nextSequence int not null,
    unique key(sectionType)
) ENGINE=InnoDB;

-- Prime it with some "sections" (we had to call them something)
insert sequences (sectionType,nextSequence) values
('Chassis',1),('Engine Block',1),('Carburetor',1),('Receipt',1001);

Stored Proc:

DROP PROCEDURE if exists getNextSequence;
DELIMITER $$ -- Note: delete this line for PHPMyAdmin (you don't need it)
CREATE PROCEDURE getNextSequence(p_sectionType varchar(200))
BEGIN
    -- pass in as a parameter the "section" for next inc, such as "Chassis"
    START TRANSACTION;
    SELECT nextSequence into @mine_to_use from sequences where sectionType=p_sectionType FOR UPDATE;
    UPDATE sequences set nextSequence=nextSequence+1 where sectionType=p_sectionType;
    COMMIT; -- get and release INTENTION LOCK ASAP
    SELECT @mine_to_use as yourSeqNum; -- return as a 1 column, 1 row resultset
END;
$$ -- Note: delete this line for PHPMyAdmin (you don't need it)
DELIMITER ; -- Note: delete this line for PHPMyAdmin (you don't need it)

Your client program will call the stored proc and process the result set getting the next num to use such as:

call getNextSequence("Receipt");
+------------+
| yourSeqNum |
+------------+
|       1001 |
+------------+

call it again for the heck of it:

call getNextSequence("Receipt");
+------------+
| yourSeqNum |
+------------+
|       1002 |
+------------+

It now has a 1 row 1 column result set with the column name yourSeqNum.

Let's pseudocode call that NNNNN as a variable.

INSERT temp(`Receipt_no`,`Name`) VALUES (NNNNN,'Fred'); -- again this is pseudocode

Id is the AUTO_INCREMENT column so we skip it in the column list above. It gets dealt with automatically by MySQL.

Why is it pseudocode? Because there is no talk here about what your front-end language is such as PHP, Python, Java, to know how you processed that result set to get the variable NNNNN. And I am not writing everything!

Your task for all that follow is to merely tweek the part above that gets that sequence number into a variable and use it in the INSERT statement.

Cleanup:

DROP SCHEMA Hybreeder;

Now there are those that would say this whole thing looks silly, for NNNNN is always 1000 greater than Id so what is the point? If you had multiple consumers of the sequence table for the receipt section, say other processes or other companies, then that would not be the case.

Please follow along the narrative over Here for more technical aspects that I glossed over above.

How to make second column increament if one column is already auto increament?

1 Answers1