How to restart id count from a auto_increment field when deleting a row

[ferpadro]

I have a table "services" with 8 fields:

 

serviceid (auto_increment)

technicianid

typeofserviceid

clientid

date

hour

hours

comments

 

Every time i add, say, 5 fields, the serviceid reaches 5. But if i erase one of them, or all of them, and then insert another row, the value of serviceid won´t replace the serviceid value for the old row. Instead of that, if i had 5 values, next row will be inserted with serviceid = 6. Is there any way to fix this?

Another problem i have is difficulty while trying to maintain integral reference. I established a composite key with the first four fields, but because serviceid changes automatically from row to row, its making incongruences with the rows inserted

To make me clear, lets say i add this two fields ('','2','1','3','2007-12-26','9:37','58','no comment'). If i add another row with the same values, the DBMS wont throw any error and insert the row. They won´t be identical to the DB engine because they diferentiate in theirs id. But two rows with the same values is not correct in real life.

One technician can´t be doing the same type of service to the same client at the same day in the same hour of the day. I dont now how to fix this. Any ideas?

 

Thanks in advance

[darkfreaks]

code please?

[tomfmason]

If you are deleting all of records in the table you could do TRUNCATE TABLE `your_table` and that will reset the auto_increment. You could also use something like SET INSERT_ID = 2; and then do your insert. Here is a simple example.

 

$query = "SET INSERT_ID = 2; INSERT INTO `your_table`(`foo`,`bar`) VALUES ('foo', 'bar');"

 

That would insert with an id of 2

[ferpadro]

I could include pieces of the code but i dont think its an issue involving the code. I thought it could be done by activating some options in the phpmyadmin control panel. I cant do what you suggested tomfmanson coz i am not able to predict what id will have the future inserted row

[rarebit]

see http://dev.mysql.com/doc/refman/5.0/en/truncate.html

It's just something you have to live with...

[rarebit]

This might help:

alter table "table_name" auto_increment=1

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

[tomfmason]

This might help:

alter table "table_name" auto_increment=1

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html

 

The code that I posted will do this without having to alter the table

 

 

$query = "SET INSERT_ID = 2; INSERT INTO `your_table`(`foo`,`bar`) VALUES ('foo', 'bar');"

 

That would insert with an id of 2

[Barand]

see

http://www.phpfreaks.com/forums/index.php/topic,140306.msg596840.html#msg596840

[sasa]

for prevent duplicate row you can add unique index to your table

ALTER TABLE `services` ADD UNIQUE `my_index` ( technicianid,
typeofserviceid,
clientid,
date,
hour,
hours,
comments )