[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql-odbc
Subject:    RE: Re: Adjusting Auto Increment values
From:       Duncan Miller <dm () dcwebworks ! com ! au>
Date:       2000-04-25 13:04:09
[Download RAW message or body]

Tre',

Yep my point exactly. The auto_increment is generally used to give each row an ID \
that is not available within the data itself, and is thus the Primary Key for the \
table. If other tables use this PK then if you were to enter completely different \
data against the same ID you would have an irrelevant reference in the other table. \
eg

User table with ID auto_increment
Order table with ID auto_increment

Order table contains reference to UserID from User table so that each Order is \
related to a User.

Should you then reassign a record in the User table to completely different (as \
opposed to modified) data a query to extract all orders for the UserID would return \
any orders for that UserID regardless of the fact that it was indeed a different \
User.

Pardon me if I am stating the obvious. What is it you are trying to achieve by having \
sequential numbers at all times? Perhaps if you have an example it may clarify your \
needs.

DM


Tremayne Smith <tre@hicksequipment.com> wrote:
Tue, 25 Apr 2000 08:19:41 -0500

> Thanx Duncan,
> 
> That's sounds very feasible and I'll give it a try.  You make a reference to a row \
> identifier which is used to maintain integrity, I assumed that that was the purpose \
> of the auto incrementing id???  I am pretty new to RDMS and I would like to get off \
> to as good a start as possible.
> I have been merely assigning the next row in my table to new entries, and these \
> entries are given a unique identifier that is generated by simply adding 1 to the \
> previous identifier. So, is there a better and more integrity sensitive and \
> convenient way of generating this identifier?  If so, please share, any advice I'll \
> take to heart and test it out with my system.  Thanx in advance. 
> Tre'
> 
> Duncan Miller wrote:
> 
> > Generally that would be unacceptable since the idea is usually to give the row an \
> > identifier and any references in other tables would be to that id, you would not \
> > longer have any integrity. Having said that I am sure that there are instances \
> > where that would not be a problem. 
> > The only way I can see that you could do it is to null the contents of a record \
> > instead of deleting it and then when adding new records check for those rows \
> > first before adding a new record. (Actually used to do that a bit with random \
> > access files). A bit of stuffing about but... 
> > HTH
> > 
> > =============================================================
> > Duncan Miller           email: danger.mouse@dcwebworks.com.au
> > Site Admin                     website: www.dcwebworks.com.au
> > dcwebworks.com.au                  Dynamic Corporate Webworks
> > =============================================================
> > 
> > Tremayne Smith <tre@hicksequipment.com> wrote:
> > Mon, 24 Apr 2000 13:45:10 -0500
> > 
> > > Hi again,
> > > 
> > > This is sort of a general question, when a record is deleted
> > > from a table that has a primary key which is an auto incrementing value,
> > > is it possible to retrieve the id that was lost. Such as if I have a
> > > table with ids(1,2,3,4) and I delete value 3, the next time I enter a
> > > record I would like for the current records to fill in the gap and force
> > > the new value to the end to the list.
> > > 
> > > Thanx for any advice.
> > > 
> > > Tre'
> > > 
> > > 
> > > ---------------------------------------------------------------------
> > > Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> > > posting. To request this thread, e-mail myodbc-thread1502@lists.mysql.com
> > > 
> > > To unsubscribe, send a message to the address shown in the
> > > List-Unsubscribe header of this message. If you cannot see it,
> > > e-mail myodbc-unsubscribe@lists.mysql.com instead.
> > > 
> > > 
> 
> 
> ---------------------------------------------------------------------
> Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
> posting. To request this thread, e-mail myodbc-thread1509@lists.mysql.com
> 
> To unsubscribe, send a message to the address shown in the
> List-Unsubscribe header of this message. If you cannot see it,
> e-mail myodbc-unsubscribe@lists.mysql.com instead.
> 
> 


---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail myodbc-thread1511@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail myodbc-unsubscribe@lists.mysql.com instead.


[prev in list] [next in list] [prev in thread] [next in thread]