Results 1 to 5 of 5
  1. #1
    Join Date
    May 2007
    Posts
    139

    Unanswered: with an AUTO INCREAMENT Field, if I delete a record what happen

    In a db table with an AUTO INCREAMENT Field, if I delete a record what happen to the count if:
    delete from
    1. end
    2. middle
    3. start

  2. #2
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Why don't you just try it?

    Btw: there is no such thing as "end", "middle" and "start" in a relational table. You are missing a fundamental concept

  3. #3
    Join Date
    May 2007
    Posts
    139
    I know DB well, and this thing knew, just pass some time with practicing other concepts in programming, and get forgot it, well since I am in design phase can you briefly answer?

    if we have records(field auto incream): 1,2,3,4,5,6,7 and delete 3 , will have 1,2,4,5,6,7 ???

    "end", "middle" and "start" === last record, record in tthe middle, start record

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by lse123 View Post
    if we have records(field auto incream): 1,2,3,4,5,6,7 and delete 3 , will have 1,2,4,5,6,7 ???
    Why don't you run this very simple statements for yourself? That would have taken less time than posting her:
    Code:
    mysql> create table lse123_test (id integer auto_increment primary key);
    Query OK, 0 rows affected (0.03 sec)
    
    mysql> insert into lse123_test values (default), (default), (default), (default), (default), (default);
    Query OK, 6 rows affected (0.00 sec)
    Records: 6  Duplicates: 0  Warnings: 0
    
    mysql> select * from lse123_test;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  3 |
    |  4 |
    |  5 |
    |  6 |
    +----+
    6 rows in set (0.03 sec)
    
    mysql> delete from lse123_test where id = 3;
    Query OK, 1 row affected (0.02 sec)
    
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)
    
    mysql> select * from lse123_test;
    +----+
    | id |
    +----+
    |  1 |
    |  2 |
    |  4 |
    |  5 |
    |  6 |
    +----+
    5 rows in set (0.00 sec)
    
    mysql>
    "end", "middle" and "start" === last record, record in tthe middle, start record
    Again: there is no such thing as a "last record" in a relational table!

  5. #5
    Join Date
    Mar 2004
    Posts
    480
    and if record 3 is deleted, there is NO reason to worry about that value "missing", the auto increment column ensures uniqueness. You don't need to worry about filling in the gaps in the sequence as many people needlessly worry about.

Posting Permissions

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