Announcement

Collapse
No announcement yet.

Not miss IDs?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Not miss IDs?

    I hope this makes some sense to some of you.
    (Done from CMS - not phpmyadmin)

    -> Add entry to database (creates ID = 1)
    -> Deleted entry (ID = 1)
    -> Add entry (ID = 2)

    ID starts from 2 even though there are no records?
    How do I start again so it creates ID = 1 and so that when I do delete and re-add it, it's ID 1 and not 2?

  • #2
    If you delete all entries in a table (with auto incrementing ID) it does not affect the auto index unless you issue this command

    TRUNCATE TABLE table_name

    This is the command issued when you click 'Empty' in phpmyadmin.

    Hope this helps.

    Scottie

    Comment


    • #3
      So only works once table is emptied?

      Comment


      • #4
        Originally posted by karimali831 View Post
        So only works once table is emptied?
        No, TRUNCATE will remove ALL records and reset the auto index number. This is equivalent to using the 'Empty' function in phpmyadmin.

        You can attempt to set the auto index by the sql command 'ALTER TABLE `table_name` AUTO_INCREMENT = 1' but if you do so while there are still records in the table, it will default to the highest value plus one.

        So, if the highest ID is 12, the auto index will be set to 13.

        TRUNCATE is the best option as it will also 'optimize' the table at the same time.

        Hope this helps,

        Scottie

        Comment


        • #5
          Yes, I understand but I need these records.

          Comment


          • #6
            ficocup_cups

            'ALTER TABLE `ficocup_cups` AUTO_INCREMENT = 1' = sql error
            TRUNCATE TABLE ficocup_cups = removes records as you said

            but when I create, delete and then re-create it still is ID 2 when suppose to be 1?

            Comment


            • #7
              Originally posted by karimali831 View Post
              ficocup_cups

              'ALTER TABLE `ficocup_cups` AUTO_INCREMENT = 1' = sql error
              TRUNCATE TABLE ficocup_cups = removes records as you said

              but when I create, delete and then re-create it still is ID 2 when suppose to be 1?
              Hi Karimali,

              Create, delete and re-create will NOT reset 'auto increment'.

              Are there other records that must not be deleted?

              If not, then use this sequence - create, truncate, create. Otherwise, you could re-create your new record with ID (your_field_name) = 1. As long as the value does not exist it will work.

              e.g. INSERT INTO `ficocup_cups` (ID, field2, field3) VALUES (1, 'foo', 'bar').

              If this doesn't help, please be a little more specific about what you are trying to do.

              Regards,

              Scottie.

              Comment


              • #8
                along with your delete command run :
                ALTER TABLE `tablename` AUTO_INCREMENT =0

                This will reset the deleted index back to the lowest available. SO if you have 2 records with ids of 1 and 2, and you delete the 2nd, the next inserted row with have an index of 2.

                Notice however that if you delete record with id1, the next inserted row will be 3. You can't 'reclaim' index 1 unless you specifically name it in the INSERT query
                Last edited by jon123; 17-02-10, 09:28 AM.

                Comment


                • #9
                  ok, thanks. That's what I wanted to know

                  Comment

                  Working...
                  X