List:General Discussion« Previous MessageNext Message »
From:Gerald Clark Date:April 19 2001 5:45pm
Subject:Re: Reference question and auto increment
View as plain text  
Why would you want to re-use the deleted ID?
That would be a poor design.

When you insert a new record with an autoinvcrement, last_insert_id()
will tell you what it
is so you can insert it in dependent tables.

Fates wrote:
> 
> I am trying to understand references in tables and using primary keys with
> auto increment option as a reference to all tables:
> 
> What is the best way to reference other tables?  I have three tables:
> menus, categories, and links.  I also have an ID field in each table that
> uses the autoincrement option and is the primary key which is used to
> reference the other tables.  This works great for referencing except when
> a record is deleted. When a record is deleted and you add another record
> the ID is not assigned the deleted records ID instead it skips the deleted
> record ID and uses the next ID when you would expect it to use the deleted
> ID which then causes problems with query to display all records from all
> tables.  Maybe if I could use REPLACE to replace the deleted record that
> might work.
> 
> I guess the solution is to create a new field common to all tables called
> record_id and not use the auto increment option and enter the record ID
> manually for each record I add.  The problem with this is I have to
> remember what the previous record was if I want it to be in order. Is
> there a better way to do this.  I like the auto increment option.
> 
> I saw an example where three tables all contain a customerid which links
> all the tables together.  Lets say I delete a customerid in one table but
> not the others. Does the database automatically delete the other
> cutomerids in the other tables that correspond to the customerid that was
> deleted or is it up to the programmer to delete the other two id's.
> 
> --
> This email was sent using w3mail.
> 
> ---------------------------------------------------------------------
> Before posting, please check:
>    http://www.mysql.com/manual.php   (the manual)
>    http://lists.mysql.com/           (the list archive)
> 
> To request this thread, e-mail <mysql-thread71882@stripped>
> To unsubscribe, e-mail
> <mysql-unsubscribe-gerald_clark=suppliersystems.com@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Thread
Reference question and auto incrementFates19 Apr
  • Re: Reference question and auto incrementGerald Clark19 Apr