1

I have a series of tables in my database that are using DATE as the primary key. I would like to reconfigure the tables so that an AUTO_INCREMENT ID field is the primary key.

What is the best way to change the tables so the first record ID in each table start at 1 and auto_increment by 1 up to, and beyond, the latest records?

Subsequently, two of the tables do not have data going back as far as the other tables so I'd like to have their starting ID fields begin at a certain integer and auto_increment from there.

  • MySQL version: 5.5.45
  • Engine: MyISAM
  • Interface: phpmyadmin
| improve this question | | | | |
  • tried several of these to no avail including 'ALTER TABLE tbl AUTO_INCREMENT = X', however on the next record addition, the ID went back to 0 instead of x @drew – Brendan Biryla Nov 5 '15 at 0:01
  • somehow I think it works. Trust me. Your schema has to have the auto inc col already in it – Drew Nov 5 '15 at 0:01
  • @Drew I dropped the latest table and added a new one with auto_inc on the primary key column (as I had done initially). I couldn't get it to take any parameters for starting number so I manually inserted two records to confirm auto increment and then updated those two records to reflect the desired starting id number. Upon the addition of a third record it appears to have continued that succession. Probably not the ideal way to go about it but it seems to work. Now i just need to figure out how to re-assign ID fields starting at 1 on my other tables – Brendan Biryla Nov 5 '15 at 0:23
  • you want to match for instance '2015-01-07' or '2015-01-07 15:32:12' – Drew Nov 5 '15 at 0:29
2

This takes one from a table that has a PK on date (or datetime, change accordingly), to a table with an auto_increment column. Then it takes a child table if you will (that relied on the previous date PK FK setup) in a new direction using the new parent table's auto_increment PK.

It could be done inline in the original tables. But I am risk adverse, and not wanting to mess up anyone's originals. At the end, do drops and renames. By the way, one interesting functionality with mysql rename is doing hot-swap renaming of tables that are safe even in high concurrency environments.

So in the below, think of origThought and nextThought as the before and after of a main table. And think of sotv1 and sotv2 as the before and after of some-other-table before and after conversion.

Create the Schema

create table origThought
(   myDate date not null, -- this can be a date or datetime, either one (but used throughout all tables consistently)
    thing varchar(10) not null,
    primary key(myDate)
);

create table nextThought
(   id int auto_increment primary key,
    myDate date not null,   -- remember that this was the driving force in old table as a PK
    thing varchar(10) not null
);

create table sotv1
(   -- some other table version 1
    id int auto_increment primary key, -- let's just say it started with this one
    ref_ToOldPK_myDate date not null,
    colC varchar(10) not null,
    constraint `asdf` foreign key (ref_ToOldPK_myDate) references origThought(myDate)
);

create table sotv2
(   -- some other table version 2
    id int auto_increment primary key,
    ref_ToOldPK_myDate date not null, -- comes along til we clean things up
    colC varchar(10) not null,
    newPkValue int not null, -- this is our new FK value pointing back to nextThought(id)
    -- when I say newPkValue, I mean the new PK value in nextThought table (not the PK value id in this table)
    constraint `asdf2` foreign key (newPkValue) references nextThought(id) -- our new PK in 'parent' table
    -- point with asdf is that asdf will fail cuz that name is already used, so named it something else if you need to
    -- many people don't care and leave out the `asdf` part
);
-- show create table sotv2; -- can see key and constraint names with this

Now load some data

insert origThought (myDate,thing) values('2015-01-02','frog'),('2015-01-07','fish');
-- convert our main table to nextThought
insert nextThought (myDate,thing) select myDate,thing from origThought order by myDate;
-- ok that is done, now onward
insert sotv1(ref_ToOldPK_myDate,colC) values ('2014-02-02',1); -- error 1452 showing FK was in force
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',77); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-07',88); -- success
insert sotv1(ref_ToOldPK_myDate,colC) values ('2015-01-02',99); -- success

Complete the conversion of sotv2 table

Now we merely want to patch our new sotv2 table using appropriate newPkValue.

Well not patch it because our FK is in place, so let's try to do it all in one insert statement.

Else it will fail due to FK (which is your friend). Your foreign key constraints are your friends.

insert sotv2(ref_ToOldPK_myDate, colC, newPkValue)
select t1.ref_ToOldPK_myDate, t1.colC, t2.id
from sotv1 t1
join nextThought t2
on t2.myDate=t1.ref_ToOldPK_myDate;

Look at results

select * from sotv2;
+----+--------------------+------+------------+
| id | ref_ToOldPK_myDate | colC | newPkValue |
+----+--------------------+------+------------+
|  1 | 2015-01-02         | 77   |          1 |
|  2 | 2015-01-07         | 88   |          2 |
|  3 | 2015-01-02         | 99   |          1 |
+----+--------------------+------+------------+

bingo, what we want to see.

Drop the prev date residue we don't need anymore in this 'child' table

alter table sotv2 drop column ref_ToOldPK_myDate;

Now we have

select * from sotv2;
+----+------+------------+
| id | colC | newPkValue |
+----+------+------------+
|  1 | 77   |          1 |
|  2 | 88   |          2 |
|  3 | 99   |          1 |
+----+------+------------+

That's even better !

| improve this answer | | | | |
  • Thanks @Drew, I'll give this a shot. Admittedly, from the looks of this, I might be in over my head on this particular endeavor. Just starting out on this path. – Brendan Biryla Nov 5 '15 at 2:13
  • you're welcome. Good luck Brendan. Do a few here and there somewhere. It might seem overwhelming, but it is probably a lot less technical than stuff you do already – Drew Nov 5 '15 at 2:15
0

Sorry, but I need to dish out lectures about the way you are abusing the database...

It is a big mistake to try to have multiple tables with AUTO_INCREMENT columns that are synchronized. Someday something will cause them to become out of sync, and you will have a mess.

If DATE is unique, keep it as the PRIMARY KEY. It is only 3 bytes; it is clear, clean, etc.

If you do need id (because DATE may not be unique), then have one of the tables AUTO_INCREMENT and the others copy the same value. To do that requires knowing how the tables are related.

You mentioned filling in old missing dates? Why bother? With LEFT JOIN and other techniques, you can handle "missing" rows in a cleaner way.

You should never depend on AUTO_INCREMENT being 1,2,3,... with no gaps. There are multiple cases where gaps will be created. (Crash; rollback; multi-master; etc.)

OK, rollback is not possible; you are using the antiquated and soon to be deprecated MyISAM. Move to InnoDB.

| improve this answer | | | | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy