CODEWITHSUNDEEP
mysqlsql
Brian Guan
Brian Guan

Reputation: 323

add auto-increment to db without changing current IDs - SQL

I have a table like this:

id   |  name 
--------------
1    | John
2    | Mary
.
.
.
NULL | Brian
NULL | Jacob

I meant to make id an AUTO INCREMENT row, but I guess I did not b/c when I added new names Brian, Jacob, it didn't automatically add incremented id values. I am wondering if there is a way to add those values to replace NULL, without changing any of the id's above it.

Upvotes: 0

Views: 201

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562240

Demo:

mysql> create table mytable (id int, name text);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into mytable values
    -> (1, 'John'),
    -> (2, 'Mary'),
    -> (NULL, 'Brian'),
    -> (NULL, 'Jacob');
Query OK, 4 rows affected (0.01 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> alter table mytable modify column id int auto_increment, add primary key (id);
Query OK, 4 rows affected (0.02 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from mytable;
+----+-------+
| id | name  |
+----+-------+
|  1 | John  |
|  2 | Mary  |
|  3 | Brian |
|  4 | Jacob |
+----+-------+
4 rows in set (0.00 sec)

Conclusion: Yes.

You do need id to be the first column in a key (I used PRIMARY KEY here). MySQL's InnoDB storage engine won't let a column be auto-increment unless it's the leftmost column in some key (that is, any index will suffice).

Upvotes: 2

Related Questions