SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2000
    Posts
    85
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello everybody,

    I am working on a project where I need to assign unique id numbers to the entries. I have a primary key column which is defined as
    id int not null auto_increment primary key

    so I thought of assigning the id number to each entry. The problem/question I have is about the deleted entries. From time to time, we will be deleting some entries and I don't know if mysql uses the emptied fields. For example, after entering 165 entries to the system, if I delete the #3 entry and then enter new ones, does the system reuse the id # 3 or does it go on with 166? I wouldn't like to re-use the emptied id numbers.

    Please let me know if my question is not clear.

    Thank you in advance

  2. #2
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The IDs are NOT reused, that's the whole point of the auto_increment field, it always counts up.

  3. #3
    SitePoint Addict
    Join Date
    Dec 2000
    Location
    BOSTON MA
    Posts
    335
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok but what if you need to insert an ID into 2 or more tables.

    i have one table where ID is auto_increment and it works fine. how can i make sure that i insert a matching set if ID's into both tables?

    i obviously keep the auto_increment in the first table, but does the ID in the second table stay as a regular field?

    thnx.
    . . . chris

  4. #4
    Database Jedi MattR's Avatar
    Join Date
    Jan 2001
    Location
    buried in the database shell (Washington, DC)
    Posts
    1,107
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    If you're using PHP as your application then you'd want to use the $new_id = mysql_insert_id( $link_id ); function to return the last inserted ID. Then you take that value and INSERT INTO someothertable VALUES( $new_id );

    Because MySQL doesn't support foreign key relationships you must make sure your application handles the verification of the other ID in updates and deletes and such.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •