I just have a question about a database table.

[Strider64]

I have updated a online trivia game and used an old database table that has existing questions.  I was wondering if the new questions would overwrite existing questions because questions have been deleted in the past with this database table?

Thanks in Advance,

      John

Edited February 4, 2023 by Strider64

[requinix]

[Strider64]

What I mean is the id field (Auto Increment) start at 1 and I have existing records that have an id of 130 lets say. I wrote new questions/answers and it started back at 1. So if I add more questions that get up to say 130 will the exist questioning be OVER WRITTEN 🙄.  If I get another rude reply I am going to delete this account and use other forums. You would think a person who didn't understand what I was writing would say could you clarify it and to top it all it's an administrator that made the reply.

Edited February 4, 2023 by Strider64

[Barand]

It won't let you INSERT a new record with the same primary key as an existing record. The only way you can overwrite them is if you UPDATE them (or INSERT with an ON DUPLICATE KEY UPDATE option)

[Strider64]

15 minutes ago, Barand said:

It won't let you INSERT a new record with the same primary key as an existing record. The only way you can overwrite them is if you UPDATE them (or INSERT with an ON DUPLICATE KEY UPDATE option)

Thank You

[gizmola]

22 hours ago, Strider64 said:

. You would think a person who didn't understand what I was writing would say could you clarify it and to top it all it's an administrator that made the reply.

I believe it was meant as a colorful reply.  Requinix is saying that things will "explode". 

What I believe will happen is that auto_increment will not work the way you expect.  

Rather than start at 1, the first allocated auto_increment value will be the largest id that exists, plus 1.  So the first new inserted value in the table will have id 31. 

There won't be an error, so I guess the main question is whether or not you actually care about the loss of available key values.

BTW, a similar issue exists with tables you might truncate or delete all values from.  That does not by itself reset the auto_increment value.  You can however, use a mysql/mariadb specific sql statement on the table to reset the auto_increment counter back to 1

ALTER TABLE tablename AUTO_INCREMENT = 1

This however would not work with Innodb tables (which is what you should always be using with mysql) as the auto_increment counter can never be set below the highest available one that exists.