Re: Increment Primary key
They aren't random at all. An AUTO_INCREMENT (or IDENTITY, as MS calls it) column does just what it says it does: it increments a counter to provide for a unique row, even if other data is the same. It will NEVER insert into a previously used row ID, nor should you force it to.
Re: Increment Primary key
Quote:
Originally Posted by
dr223
I want to amend this so that when a record is deleted then it reads the most present last record and increment the pay_id
Why? What purpose would this serve?
Re: Increment Primary key
It's neat?
Technically, you can do this by altering the settings for the primary key field, but don't do it.
Re: Increment Primary key
Quote:
Originally Posted by
dr223
2 John 7836434
3 Ali 3473443
4 James 8243245
What if you delete records from in between (say record #3). Won't you still have records out of order?
Re: Increment Primary key
What does it matter if the ID's are neatly in order without skipping numbers? The ID is usually just a number to identify that particular record with. The user, nor you really, should never have to even see it. Your application should function the same way whether the ID's are neatly in order or not.
If you still REALLY want to do this, perhaps an option is to create another column, DELETED, which tells you if that particular record is deleted or not. This way, you never actually delete a record, but you merely set the DELETED flag (set it to 1, for example, when 'deleted', and 0 otherwise). Then, in all your queries, you simply add a where clause "where DELETED = 0". The behavior will be the same as if you had really deleted the rows, and your ID's should remain neatly in order, and you have to added advantage of the customer never losing records, as they are still in the table. You could then create a simple 'restore' functionality that resets the DELETED flag of any deleted records. Obviously, this may not be an option if you are working with huge tables where you simply need to remove records to be able to cope with the size.