Are you saying just change the row with the 0 value as the PK, and change the FK's in the related tables to point to the new value instaed of 0?
If so, would this move the row logically to the end of the table, if the 0 PK was replaced with the next auto_increment value? I suppose that would be alot easier than trying to bump the PK and related FK values of the whole table by 1, just to give the first row in the table the auto_increment value of 1? What about before migrating the database, just adding a new row to the end of the table, that would duplicate the data in the first row, then deleting the first row from the table? Would that work? Keith In theory, theory and practice are the same; in practice they are not. On Thu, 30 Mar 2006, Daniel Kasak wrote: > To: "Stanton, Brian" <[EMAIL PROTECTED]>, > "'mysql@lists.mysql.com'" <mysql@lists.mysql.com> > From: Daniel Kasak <[EMAIL PROTECTED]> > Subject: Re: auto_increment and the value 0 > > Stanton, Brian wrote: > > I'm migrating a database from 4.0.12 on Solaris to 4.0.18-0 on Red > > Hat > > Linux. A few of the tables have a 0 (zero) in the auto_increment > > primary > > key column. However, when importing, the 0 in the insert is > > translated to > > the next available auto_increment value thus causing a duplicate key > > situation on the next value in the import. I've tried removing the 0 > > row > > from the export and adding it in manually afterwards, but that also > > translates the 0 to the next available auto_increment value. I've > > also > > tried creating the table with the table option AUTO_INCREMENT=0 and > > inserting the 0 row first. That also translated it to a value of 1 > > and > > caused duplicate keys. > > > You can either: > - create the table without the auto_increment field, load the data, and > add the auto_increment field, or > - change all your zero values *now* ( and related fields in other tables > ), back things up, and then move the data > > I would take the 2nd option. > > -- > Daniel Kasak > IT Developer > NUS Consulting Group > Level 5, 77 Pacific Highway > North Sydney, NSW, Australia 2060 > T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 > email: [EMAIL PROTECTED] > website: http://www.nusconsulting.com.au k -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
