List:General Discussion« Previous MessageNext Message »
From:Josh S. Gough Date:April 2 1999 9:14pm
Subject:Re: Autoincrement Field Loses Count
View as plain text  
I was just about to make the same post...I have the same thing
occuring in 
mysql  Ver 9.29 Distrib 3.22.19a, for pc-linux-gnu (i686)

However, mine doesn't care about the table being emptied, it will just
take the largest value and add one to it, so when I then delete the
largest value, inserting a new record takes that largest value again.
This isn't what I want and from some posts I've read, it shouldn't do this
at all actually.
here is a sample: (nevermind the nonsensical 'year' value hehe)

mysql> select * from report;
+----+-----------+--------+------+------------+
| id | person_id | set_id | year | allow_edit |
+----+-----------+--------+------+------------+
|  1 |         1 |      1 | eee  |          1 |
|  2 |         1 |      1 | eee  |          1 |
|  3 |         1 |      1 | eee  |          1 |
|  4 |         1 |      1 | eee  |          1 |
|  5 |         1 |      1 | eee  |          1 |
+----+-----------+--------+------+------------+
mysql> delete from report where id = 5

mysql> select * from report;
+----+-----------+--------+------+------------+
| id | person_id | set_id | year | allow_edit |
+----+-----------+--------+------+------------+
|  1 |         1 |      1 | eee  |          1 |
|  2 |         1 |      1 | eee  |          1 |
|  3 |         1 |      1 | eee  |          1 |
|  4 |         1 |      1 | eee  |          1 |
+----+-----------+--------+------+------------+

mysql> insert into report values(null, 1, 1, 'eee', 1);
mysql> insert into report values(null, 1, 1, 'eee', 1);

mysql> select * from report;
+----+-----------+--------+------+------------+
| id | person_id | set_id | year | allow_edit |
+----+-----------+--------+------+------------+
|  1 |         1 |      1 | eee  |          1 |
|  2 |         1 |      1 | eee  |          1 |
|  3 |         1 |      1 | eee  |          1 |
|  4 |         1 |      1 | eee  |          1 |
|  5 |         1 |      1 | eee  |          1 |
|  6 |         1 |      1 | eee  |          1 |
+----+-----------+--------+------+------------+


I was hoping to see 1, 2, 3, 4, 6, 7

Anyone have any ideas?
The orignal report table definition is like this:

CREATE  TABLE report (
id INT(7) PRIMARY KEY NOT NULL AUTO_INCREMENT,
person_id INT(7) NOT NULL, 
set_id INT(7) NOT NULL,
year VARCHAR(5) NOT NULL,
allow_edit BOOL NOT NULL,

FOREIGN KEY (person_id) REFERENCES person (id),
FOREIGN KEY (year) REFERENCES year(year)
);
CREATE UNIQUE INDEX unique_per_year ON report (person_id, year);

Thanks,
-JG

Thread
Autoincrement Field Loses CountJose de Leon3 Apr
Re: Autoincrement Field Loses CountJosh S. Gough3 Apr
  • Re: Autoincrement Field Loses CountVivek Khera3 Apr
    • Re: Autoincrement Field Loses CountJosh S. Gough3 Apr
      • Re: Autoincrement Field Loses CountPaul DuBois3 Apr