469,603 Members | 2,148 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,603 developers. It's quick & easy.

Loading initial values into auto_increment column

2
I am migrating a database to MySQL 5.0. Several tables should have their primary key as an auto_increment integer. The database being copied has records with the primary key beginning with zero (0). These keys are foreign keys so it is not desirable to change their values.

Here is what I have been trying:
(1) Truncate table <tablename>;
(2) Alter table <tablename> modify recid integer not null default 0;
(3) <insert rows into table>
(4) Alter table <tablename> modify recid integer not null auto_increment;

When I execute statement number 4, above, if the recid has been inserted with a value of zero, then MySql is changing the zero to one.

How can I get MySql to let me load my values into the column and to set the auto_increment to the max(recid) + 1 ?
Apr 15 '07 #1
1 1489
bcm
2
I believe the answer is to use the Sql-Mode setting NO_AUTO_VALUE_ON_ZERO.

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

http://www.xaprb.com/blog/2006/04/23/explicit-inserts-into-auto_increment-columns-in-mysql/
Apr 16 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Nico v. Rossum | last post: by
2 posts views Thread by hjyn | last post: by
2 posts views Thread by Norvin Laudon | last post: by
5 posts views Thread by John Richardson | last post: by
1 post views Thread by John Cosmas | last post: by
1 post views Thread by Phil Latio | last post: by
2 posts views Thread by Stefan van Roosmalen | last post: by
reply views Thread by guiromero | last post: by
reply views Thread by devrayhaan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.