Model of the next available primary key

Question

How can I find out the next free primary key of some model?

Answer 1

Even if you can request the next available primary key value, this will not help you. If you do not lock the table, you cannot use this value before any other database client can capture it for its insertion.

Instead, you just need to insert your row, and then you can request the most recent key value generated during the current session. Each database that supports automatically generated primary keys provides a method for retrieving the most recent key inserted during a session.

The in-session part is important because it protects your session from any insertions performed by other clients at the same time. They can generate key values, and your session will continue to report the same value that it inserted recently.

@Stuart Childs assumes MySQL generates the following identifier with MAX(column_name)+1 , but this is not true. Let's say you insert a row and generate an ID value. But you roll back this insert, and then DELETE this line. The next time you insert, MySQL will generate a new ID value. Thus, the ID value is greater than the last identifier value generated by any client, regardless of which rows are currently stored in the table.

Similarly, if you insert, but do not commit immediately. Before committing, any other client makes an insert. Both your session and another client session will have their own unique identifier value. Automatically generated primary keys work independently of transaction isolation to ensure uniqueness.

Automatically generated primary key values ​​are not reused or assigned to more than one session, even if you have not done your insertion yet, or if you canceled the insertion or deleted the row.

Answer 2

-Using Django and Mysql

next_id = Table.objects.filter (id__gt = current_id) [0] .id

Answer 3

I had the same question because I was duplicating an element and I wanted to change its pk to a new one so that it could be inserted.

My solution was as follows:

 import copy objectCopy=copy.copy(object) objectCopy.pk=None objectCopy.save() 

I mean, if save() does this for you, why do you want to do it manually?

Answer 4

next_id = User.objects.order_by('-id').first().id + 1

You may need to handle it if nothing exists.

Or with the maximum request:

next_id = User.objects.all().aggregate(Max('id'))['id__max'] + 1

Answer 5

I think you will have to execute some kind of custom SQL . The SQL you need will be database specific. As far as I know, there is no reliable way to do this in MySQL. You can get the last insert identifier, but it depends on the session (i.e. you can get the identifier of the last record inserted into the database session, but not the last identifier for another session that may have occurred after yours). However (and someone correct me if I am wrong), MySQL generates the following identifier with MAX (column_name) + 1 so you can do the same.

PostgreSQL simplifies work with sequence manipulation functions .

Edit:

Turns out I was thinking about AUTO_INCREMENT columns in a multi- column key that are generated with MAX (column_name) +1. However, the dot still claims that there is no way to get the next identifier before it is generated by the insert, which was the original question.

Answer 6

this snippet will certainly help you.