I have an existing sql table with 3 columns and 100+ entries/rows. There is an id column with autoincrement.
Now, I want to add 10 new rows at the beginning of the table with id from 1 to 10. But I cannot lose any existing row. So, how do I do it?
One idea that just came to my mind is perhaps I can increase the existing id by adding 10, like 1+10 becomes 11, 25+10 becomes 35, and then I can add rows at the beginning. What will be the script for this IF this is possible?
All you need to do for this is to set the auto_increment for that table to whatever number you need to create space for the new records you want to insert.
For example, if you inserted rows with id's 1-100, you might:
Check the next auto_increment value by running:
select auto_increment as val from information_schema.tables where table_schema='myschema' and table_name='mytable';
Let's assume that value would be 101 (the value that would be used if you inserted a new row). You can "advance" the auto_increment value by running:
alter table myschema.mytable auto_increment = 111;
If you insert a new row like this:
insert into mytable (not_the_id_column) values ('test');
It will get the "next" id of 111. But if you specify id values manually, you are ok in this case as long as you use any value less than 111, so you could insert your desired records like this:
insert into mytable (id, not_the_id_column) values (101, 'test101');
insert into mytable (id, not_the_id_column) values (102, 'test102');
... -- more inserts as needed
Now, you still must take proper precautions when updating PK values, or any value that has dependencies on it (Foreign Key or otherwise), but it is completely legitimate to forcibly advance and/or backfill the id values, as long as the resulting auto_increment value doesn't duplicate one that's already in the table.