Auto increment

[Viji nellaiappan]

Greetings to all,

this is my query for creating table..

CREATE TABLE `books` (
`serial_num` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT ,
`bk_name` VARCHAR( 200 ) NOT NULL ,
`qty` INT( 10 ) NOT NULL ,
`author` VARCHAR( 25 ) NOT NULL ,
PRIMARY KEY ( `serial_num` )
) 

this is my table structure...

+------------+------------------+------+-----+---------+----------------+
| Field      | Type             | Null | Key | Default | Extra          |
+------------+------------------+------+-----+---------+----------------+
| serial_num | int(10) unsigned |      | PRI | NULL    | auto_increment |
| bk_name    | varchar(200)     |      |     |         |                |
| qty        | int(10)          |      |     | 0       |                |
| author     | varchar(25)      |      |     |         |                |
+------------+------------------+------+-----+---------+----------------+
 

if i delete any row in this table..then the auto increment does not works properly...for e.g. if i delete 2nd row means.. then the serial number for books are 1,3,4,5..how to insert new row with serial number 2 ? how to resolve this? please help me..


_______________________
If not now,then never....

Regards,
Viji nellaiappan

[r035198x]

Why is it important for you to insert at id 2? Why not let it auto increment and insert 6 instead?

[Viji nellaiappan]

@r035198x
while displaying record one by one using serial number it shows error for 2nd row..so i want to insert a record in that id..


______________
If not now,then never....


Regards,
Viji nellaiappan.

[r035198x]

How are you displaying the records by using serial number? You should be making sure that it works even if there is no record with id 2 in there. In general if you set a column to auto_increment, you should not be concerned about what values that column takes. It should all be managed by MySQL. If you want a column whose values you can cntrol, then define another column for that in the table and leave the auto_increment one to MySQL's amusement.