Taken from the MYSQL forum:
I have two different databases they both have an auto-increment PK field and while they are different databases with different names, they do have tables with the same names. What I find is that the auto-crement integer number remembers what it is across these databases. So if I insert in the 1st DB and the number is 24, then I insert in the 2nd DB the number will be 25. I would of thought that the number would be "fresh" and independent between the DBs. Could you explain? Here's the link:http://lists.mysql.com/mysql/178855Rick Sanders
On 2/4/05 12:05 PM, "Rick Sanders" <[EMAIL PROTECTED]> wrote:
Auto Incementing numbers are good, but don't work if you want to do a
relational query between 2 tables (Customers & Orders) and if you delete
an
item from the database, it re-numbers all the records.
I don't think so. What flavor database you using?
I'm talking about the default settings on MS SQL server, MYSQL, and Access.
As far as I know, Pervasive SQL & Oracle don't re-number the records.
Default on mysql is to leave those numbers alone. You delete one and you
have a gap in the sequence. The databases may reuse the location in the
database so that if you just list records, they will appear out of sequence
(if you have records 1-1000 and delete # 3, then record #1001 will appear
right after #2 in an unsorted list.), but I think the norm for all databases
is to leave the auto-incremented unique numbers alone. They lose their value
if they jitter around.
Regarding relational queries, you can't do a relational query between 2 database tables that have the primary key as an auto-incrementing number, unless you really modify the queries and know what you're doing.
For each table, you create a UID field, which is the auto-incrementing unique integer.
For child tables, you have a field, which is the parent_UID so that you can
make the many:one relation.
Then you make the join where child.parent_uid = parent.uid
I did this
successfully a year ago, but found it was more hassle than just keeping a
numeriv field and assigning unique numbers by using the application, which
is standard for windows programs and accounting packages.
It's no more difficult than any other unique number. When you create the new
record and retrieve the new UID, you've got the order number. You can send
that to the customer, put it in a log, have it display to fulfillment or
customer service. You can fetch the record with one argument. It has some
extra benefits of making it very easy to see the order sequence, i.e., order
number 234 came right before 235. So it's not only unique, but provides some
other information about the record.
I'm thinking you're using terms differently than we are or not quite
comfortable with setting up joins. You CAN create UIDs by other means, but
the database gives you a readymade mechanism to do this, so why not take the
easy way out?
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
________________________________________________________________________ TO UNSUBSCRIBE: Go to http://www.witango.com/developer/maillist.taf
