[EMAIL PROTECTED] wrote:
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?
Yes.
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?
Not really. The primary key isn't ( or at least shouldn't be ) used to
determine 'position' in the record. If you are actually using the
primary key to determine 'position', ie select * from table order by
primary_key ... then yes, the position will change. But the record is
still in there, and still linked to related records ( as long as you
update their foreign key values to the new value as well ). So as long
as you don't depend on the primary key in something like the above ( ie
you're not using the primary key to determine a record's age ), then it
doesn't really matter what primary key it has. If you are depending on
this value in an 'order by' clause, then find some work-around - order
by something else - before you change it.
Also keep in mind that when you do a select without an order by clause,
you aren't guaranteed of getting the records in any particular order
anyway - the DB server is allowed to return records in any order if you
don't specify an 'order by' clause.
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?
Yes. That sounds messy.
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?
Yes but it would be no different to just updating the primary key of the
existing record.
Either way, you will have issues with restoring from backups if you keep
an auto_increment column with a zero value around ( as you've discovered
), so what ever you do, you need to get rid of those zero values.
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
