Created
October 26, 2019 17:45
-
-
Save Arqam123/6a91fa47974ba3c05de9619795352f33 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
1) Create separate table for generate auto incremented ids. Lets name it customer_seq | |
############################################################################## | |
CREATE TABLE Customer_seq | |
( | |
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY | |
); | |
############################################################################## | |
2) Create Customer table | |
############################################################################## | |
CREATE TABLE Customer | |
( | |
id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30) | |
); | |
############################################################################## | |
3) Create trigger | |
############################################################################## | |
DELIMITER $$ | |
CREATE TRIGGER tg_table1_insert | |
BEFORE INSERT ON Customer | |
FOR EACH ROW | |
BEGIN | |
INSERT INTO Customer_seq VALUES (NULL); | |
SET NEW.id = CONCAT('CUS-', LPAD(LAST_INSERT_ID(), 3, '0')); | |
END$$ | |
DELIMITER ; | |
############################################################################## | |
4) Test with sample data | |
############################################################################## | |
INSERT INTO Customer (name) | |
VALUES ('Jhon'), ('Mark'); | |
############################################################################## | |
Reference url: https://stackoverflow.com/questions/17893988/how-to-make-mysql-table-primary-key-auto-increment-with-some-prefix?answertab=votes#tab-top | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment