Asked  7 Months ago    Answers:  5   Viewed   36 times

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'.

I have:

table: course
fields: courseID, courseName

Example: number of records in the table: 18. If I delete records 16, 17 and 18 - I would expect the next record entered to have the courseID of 16, however it will be 19 because the last entered courseID was 18.

My SQL knowledge isn't amazing but is there anyway to refresh or update this count with a query (or a setting in the phpMyAdmin interface)?

This table will relate to others in a database.


Given all the advice, I have decided to ignore this 'problem'. I will simply delete and add records whilst letting the auto increment do it's job. I guess it doesn't really matter what the number is since it's only being used as a unique identifier and doesn't have a (as mentioned above) business meaning.

For those who I may have confused with my original post: I do not wish to use this field to know how many records I have. I just wanted the database to look neat and have a bit more consistency.

 Answers

24

What you're trying to do sounds dangerous, as that's not the intended use of AUTO_INCREMENT.

If you really want to find the lowest unused key value, don't use AUTO_INCREMENT at all, and manage your keys manually. However, this is NOT a recommended practice.

Take a step back and ask "why you need to recycle key values?" Do unsigned INT (or BIGINT) not provide a large enough key space?

Are you really going to have more than 18,446,744,073,709,551,615 unique records over the course of your application's lifetime?

Tuesday, June 1, 2021
 
pamelus
answered 7 Months ago
61

When I submitted ticket to clearDB, They replied me like this:

ClearDB uses circular replication to provide master-master MySQL support. As such, certain things such as auto_increment keys (or sequences) must be configured in order for one master not to use the same key as the other, in all cases. We do this by configuring MySQL to skip certain keys, and by enforcing MySQL to use a specific offset for each key used. The reason why we use a value of 10 instead of 2 is for future development.

Saturday, May 29, 2021
 
axiomer
answered 7 Months ago
46

This will give you a consecutive row number with 3.

SELECT
    (@cnt := @cnt + 1) AS rowNumber,
    t.rowID
FROM myTable AS t
  CROSS JOIN (SELECT @cnt := 0) AS dummy
WHERE t.CategoryID = 1
ORDER BY t.rowID ;

Result

| ROWNUMBER | ROWID |
---------------------
|         1 |     1 |
|         2 |    25 |
|         3 |    33 |
|         4 |   150 |
Tuesday, July 27, 2021
 
csi
answered 5 Months ago
csi
85

No, I don't think this is possible.

You can create a UNIQUE INDEX which has essentially the same effect as a PRIMARY KEY:

CREATE UNIQUE INDEX pk_index ON "table1"("field1","field2");

Besides, I fail to see the logic of your schema, that is -> if a column is autoincrement and you don't intend to mess with the values manually, it's going to be unique anyway, so it makes a good simple short primary key. Why the composite? You may have good reasons to make another index on the combination of columns, though.

Wednesday, July 28, 2021
 
nomie
answered 5 Months ago
20

You can definitely do this with a computed column.

Either, if you stick with your two tables, you could just simply add a single computed, persisted column like this:

ALTER TABLE Local_Customer
  ADD CustomerID AS 'LOC' + CAST(ID AS VARCHAR(7)) PERSISTED

ALTER TABLE International_Customer
  ADD CustomerID AS 'INT' + CAST(ID AS VARCHAR(7)) PERSISTED

If you decide to have a single table with a discrimator column, e.g. "IsDomestic" of type BIT, you could do a single computed column that will use "LOC" or "INT" as prefix, based on the value of the "IsDomestic" column:

ALTER TABLE Customer
  ADD CustomerID AS CASE IsDomestic WHEN 0 THEN 'INT' ELSE 'LOC' END + CAST(ID AS VARCHAR(7)) PERSISTED

Either way - your "ID" field of type INT IDENTITY will be automatically increased for each row, and the computed column will create a more human-readable "CustomerID" automagically, without any further effort on your part.

Friday, August 27, 2021
 
tsmbl
answered 4 Months ago
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :
 
Share