reusing unique id's when deleting/inserting?

[Peter R. Wood - Mailing Lists]

Hi all,

Here are two tables I'm working with. I apologize if you 
are not using a monospaced font and they are messed up.

This is used by a gradebook program.  When a professor adds an assignment to 
a class he teaches, it inserts the information about the assignment into the 
assignments table, and inserts the grade each student receives in the 
student_assignment table. This happens at the same time so the unique ID is 
generated the same.

When he deletes an assignment from the class, it deletes it from both tables.

However, after this, if he adds an assignment again, the unique ID generated 
by insertion into the assignment table is one greater than it should be - 
i.e., it auto increments the id based on the id of the row that just got 
deleted, not on the last row that is actually in the table. When the 
assignment is inserted into the student_assignment table, the unique ID is 
auto incremented based on the last ID that is actually in the table, not on 
the row that just got deleted.

The workaround I have found is to use last_insert_id() to find the ID that 
got inserted in to the assignment table, and use this to manually specify the 
ID for the student_assignment table.  This has the effect of putting the ID's 
in sync, which is what I want, but it seems like there should be a better 
solution. Any suggestions?

Thanks,

Peter

(tables below)


mysql> describe assignments;
+------------+------------+------+-----+---------+----------------+
| Field      | Type       | Null | Key | Default | Extra          |
+------------+------------+------+-----+---------+----------------+
| id         | int(30)    |      | PRI | NULL    | auto_increment |
| title      | char(50)   | YES  |     | NULL    |                |
| class_num  | char(6)    |      |     |         |                |
| weight     | float(4,2) | YES  |     | NULL    |                |
| max_points | int(3)     |      |     | 0       |                |
| type       | char(20)   | YES  |     | NULL    |                |
+------------+------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
 
mysql> describe student_assignment;
+-----------+---------+------+-----+---------+----------------+
| Field     | Type    | Null | Key | Default | Extra          |
+-----------+---------+------+-----+---------+----------------+
| stu_id    | int(10) |      | PRI | 0       |                |
| assign_id | int(30) |      | PRI | NULL    | auto_increment |
| grade     | int(3)  | YES  |     | NULL    |                |
+-----------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


---------------------------------------------------------------------
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 <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php