How to set primary key, then convert to autofield?

Question

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

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

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

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

While it may seem strange that we need the DocumentCluster pk values to match the foreign key values there is an important reason. We use the Document pk in our URLs, but after the change the URLs will load a DocumentCluster, not a Document, so we'll need the pk in DocumentCluster to be the same as it was in Document.

Once that's done, I want the PK of the DocumentCluster to be an AutoField, incrementing from the highest value that was migrated.

Can this be done?

Answer 1

You cannot have a FK from DocumentCluster.pk to Document and at the same time make DocumentCluster.pk a serial column. That's a contradiction. It would have to be two separate columns. I suppose you mean the other way round: from Document to DocumentCluster.

This can be done with 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 be using double-quoted names like "Document", in which case you'd have to do the same ...

• About setval().

• About pg_get_serial_sequence()

• The PK could be declared in the CREATE TABLE statement, but it's cheaper to add it after rows have been inserted - assuming pk is unique not null.

More explanation and links:

• Manually update the counter used for generation of primary key in Hibernate?
• How to reset postgres' primary key sequence when it falls out of sync?
• Modify Django AutoField start value