How to set primary key and then convert to autofield?

Question

I have a model called Document , and I want to add a new DocumentCluster table that is above it, with the Document foreign key.

 class DocumentCluster(models.Model): sub_document = models.ForeignKey(Document) ...lots of fields here... 

When I add this table using South, I need to populate it by setting the primary key and foreign key to the same value.

For example, if I currently have a Document object with pk of 12, the new DocumentCluster object will have pk of 12 and the foreign key for Document number 12.

Although it may seem strange that we need DocumentCluster pk values ​​to match foreign key values, there is an important reason. We use Document pk in our URLs, but after changing the URLs, they load DocumentCluster , not Document , so we need the pk in DocumentCluster to be the same as it was in Document .

After that, I want PK DocumentCluster be AutoField, increasing from the highest value that was ported.

Can this be done?

Answer 1

You cannot have FK from DocumentCluster.pk to Document and at the same time make DocumentCluster.pk a serial column. This is a contradiction. These must be two separate columns. I suppose you mean the opposite: from Document to DocumentCluster .

This can be done using SQL DDL commands:

 BEGIN; CREATE TABLE DocumentCluster (pk serial, lots text, of_fields int, ...); INSERT INTO DocumentCluster (pk, lots, of_fields, ...) SELECT pk, lots, of_fields, ... FROM Document ORDER BY pk; -- optional ALTER TABLE DocumentCluster ADD CONSTRAINT DocumentCluster_pkey PRIMARY KEY (pk); -- could be integrated in CREATE TABLE ALTER TABLE Document -- and not the other way round! ADD CONSTRAINT pk_fk FOREIGN KEY (pk) REFERENCES DocumentCluster(pk); SELECT setval(pg_get_serial_sequence('DocumentCluster', 'pk'), max(pk)) FROM DocumentCluster; -- update SEQUENCE to highest value COMMIT; 

• Your obfuscation layer (Django) may use double-quoted names, such as "Document" , in which case you will have to do the same ...

• About setval() .

• About pg_get_serial_sequence()

• PK can be declared in the CREATE TABLE statement, but adding it after adding rows means pk is unique not null .

Additional explanations and links:

• Manually update the counter used to generate the primary key in Hibernate?
• How does the primary sequence reset postgres' when it drops out of sync?
• Change Django AutoField Initial Value

Answer 2

Using the south is much easier than I thought.

Use the south schema command, as usual, to create a schema migration that adds the DocumentCluster model.

Then use the datamigration command to create the skeleton for the following transition:

 ./manage.py datamigration yourapp populate_clusters 

Then fill in the resulting method in the python migration file so that it looks like this:

 def forwards(self, orm): max_id = -1 clusters_added = 0 for document in orm.Document.objects.all(): cluster = orm.DocumentCluster() cluster.id = document.id cluster.sub_document = document cluster.save() max_id = max(max_id, document.id) clusters_added +=1 if max_id >= clusters_added: orm.DocumentCluster.objects.raw("SELECT "\ "setval(pg_get_serial_sequence('yourapp_documentcluster',"\ "'id'), %d)" % max_id+1) 

(The reverse method in this datamigration will simply delete all instances of the DocumentModel.)

If this populate_clusters migration step starts immediately after the transition step that adds the DocumentCluster table, the DocumentCluster table is empty and the serial / autokey counter starts from zero. If you have never deleted any documents, the value of the serial / autokey counter will end at the next unused value for Document pk values, and you won’t even have to raise it, as shown in Erwin's answer.

If, however, you deleted Document instances, or if some id value was missing, you will need to activate this serial / autokey counter using SQL. Django provides the ability to directly execute raw SQL. (I do not think that you can do without using raw SQL).

For security reasons, you should check if cluster.id is <= the maximum value of document.id in the loop.