Auto Increment Table Column

How to make MySQL table primary key auto increment with some prefix

If you really need this you can achieve your goal with help of separate table for sequencing (if you don't mind) and a trigger.

Tables

CREATE TABLE table1_seq
(
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
);
CREATE TABLE table1
(
  id VARCHAR(7) NOT NULL PRIMARY KEY DEFAULT '0', name VARCHAR(30)
);

Now the trigger

DELIMITER $$
CREATE TRIGGER tg_table1_insert
BEFORE INSERT ON table1
FOR EACH ROW
BEGIN
  INSERT INTO table1_seq VALUES (NULL);
  SET NEW.id = CONCAT('LHPL', LPAD(LAST_INSERT_ID(), 3, '0'));
END$$
DELIMITER ;

Then you just insert rows to table1

INSERT INTO Table1 (name) 
VALUES ('Jhon'), ('Mark');

And you'll have

|      ID | NAME |
------------------
| LHPL001 | Jhon |
| LHPL002 | Mark |

Here is SQLFiddle demo

How to add auto_increment field to a table with huge data?

I'd go:

-- drop PK as AUTO increment demands being the PK
ALTER TABLE table_test DROP PRIMARY KEY;
-- add Id as PK and the first column
ALTER TABLE table_test ADD Id INT AUTO_INCREMENT PRIMARY KEY FIRST;
-- make sure the old PK is unique
ALTER TABLE table_test ADD CONSTRAINT table_test_uk UNIQUE (ad_id, country);

or perhaps:

CREATE TABLE table_test2 LIKE table_test;
ALTER TABLE table_test2 DROP PRIMARY KEY;
-- add Id as PK and the first column
ALTER TABLE table_test2 ADD Id INT AUTO_INCREMENT PRIMARY KEY FIRST;
-- make sure the old PK is unique
ALTER TABLE table_test2 ADD CONSTRAINT table_test2_uk UNIQUE (ad_id, country);
INSERT INTO table_test2(ad_id, account_id, country, image_hash)
SELECT ad_id, account_id, country, image_hash FROM table_test;
DROP TABLE table_test;
RENAME TABLE table_test2 TO table_test;

How to add auto increment column in existing table in mysql which starts from 1000 and increment by 1

You might just want to do this in one statement.

+-------+---------+--------+---------------------+
| subid | clickid | status | datetime            |
+-------+---------+--------+---------------------+
|     1 |     123 | low    | 2018-07-24 20:20:44 |
|     2 |     123 | act    | 2018-07-24 21:20:44 |
|     3 |     231 | act    | 2018-07-25 20:20:44 |
|     4 |     231 | low    | 2018-07-25 21:20:44 |
|     5 |     789 | low    | 2018-07-26 20:20:44 |
|     6 |     789 | act    | 2018-07-26 21:20:44 |
+-------+---------+--------+---------------------+
6 rows in set (0.00 sec)

ALTER TABLE T ADD COLUMN RowId  INT AUTO_INCREMENT NOT NULL PRIMARY KEY FIRST,
AUTO_INCREMENT = 1000;

+-------+-------+---------+--------+---------------------+
| RowId | subid | clickid | status | datetime            |
+-------+-------+---------+--------+---------------------+
|  1000 |     1 |     123 | low    | 2018-07-24 20:20:44 |
|  1001 |     2 |     123 | act    | 2018-07-24 21:20:44 |
|  1002 |     3 |     231 | act    | 2018-07-25 20:20:44 |
|  1003 |     4 |     231 | low    | 2018-07-25 21:20:44 |
|  1004 |     5 |     789 | low    | 2018-07-26 20:20:44 |
|  1005 |     6 |     789 | act    | 2018-07-26 21:20:44 |
+-------+-------+---------+--------+---------------------+
6 rows in set (0.00 sec)

show create table t;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                               |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `RowId` int(11) NOT NULL AUTO_INCREMENT,
  `subid` int(11) DEFAULT NULL,
  `clickid` int(11) DEFAULT NULL,
  `status` varchar(3) DEFAULT NULL,
  `datetime` datetime DEFAULT NULL,
  PRIMARY KEY (`RowId`)
) ENGINE=InnoDB AUTO_INCREMENT=1006 DEFAULT CHARSET=latin1 |

disable/remove auto increment attribute in snowflake

"Drop default" is doing the trick:

alter table abc alter id drop default;

How to get the generated ID of an auto increment column after insertion of a record in HSQLDB within same connection?

You can use the identity() method in the next INSERT statement for the child table. In this example the child table has a ref_id column for the foreign key plus a data_col for its data. The child table has its own primary key column and a foreign key column that references the log_id column of the test table.

create table child(child_id integer identity primary_key, ref_id integer, data_col varchar);
alter table child add constraint child_fk foreign key (ref_id) references test(log_id);
insert into child(ref_id, data_col) values (identity(), ?);

How to have an auto increment column in MySQL table without making it a primary key?

Consider:

CREATE TABLE insurance (
    _id INT(5) UNSIGNED AUTO_INCREMENT,
    patient_id INT(5) UNSIGNED NOT NULL,
    iname VARCHAR(40) DEFAULT NULL,
    from_date DATE DEFAULT NULL,
    to_date DATE DEFAULT NULL,
    PRIMARY KEY (patient_id),
    KEY(_id),
    CONSTRAINT fk_insurance FOREIGN KEY (patient_id)
    REFERENCES patient(_id)
);

That is, the auto-incremented column must at least be a key. You can still use another column as the primary key if you like.

---

Related Topics