Django migration creating copy tables

I am getting my hands wet with Django and I created a few models with some relationships between them, I inspected the migration and it appears as though Django is overcomplicating the migration.

Here is a sample of my models

from django.db import models
​
​
class Product(models.Model):
    id = models.CharField(max_length=15, primary_key=True)
    name = models.CharField(max_length=100)
    cost = models.DecimalField(max_digits=19, decimal_places=4)
​
​
class Store(models.Model):
    name = models.CharField(max_length=25, primary_key=True)
    order_commission = models.DecimalField(max_digits=4, decimal_places=2)
​
​
class Listing(models.Model):
    product = models.ForeignKey(Product, on_delete=models.RESTRICT)
    store = models.ForeignKey(Store, on_delete=models.RESTRICT)
    price = models.DecimalField(max_digits=19, decimal_places=4)
​
​
class Sale(models.Model):
    listing = models.ForeignKey(Listing, on_delete=models.RESTRICT)
    quantity = models.IntegerField()
    price = models.DecimalField(max_digits=19, decimal_places=4)
    tax = models.DecimalField(max_digits=19, decimal_places=4)

This is the SQL Django generates for the migration (I get the SQL via $ ./manage.py sqlmigrate foo 0001)

BEGIN;
--
-- Create model Listing
--
CREATE TABLE "foo_listing" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "price" decimal NOT NULL
);
--
-- Create model Product
--
CREATE TABLE "foo_product" (
  "id" varchar(15) NOT NULL PRIMARY KEY,
  "name" varchar(100) NOT NULL,
  "cost" decimal NOT NULL
);
--
-- Create model Store
--
CREATE TABLE "foo_store" (
  "name" varchar(25) NOT NULL PRIMARY KEY,
  "order_commission" decimal NOT NULL
);
--
-- Create model Sale
--
CREATE TABLE "foo_sale" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "quantity" integer NOT NULL,
  "price" decimal NOT NULL,
  "tax" decimal NOT NULL,
  "listing_id" integer NOT NULL REFERENCES "foo_listing" ("id") DEFERRABLE INITIALLY DEFERRED
);
--
-- Add field product to listing
--
CREATE TABLE "new__foo_listing" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "price" decimal NOT NULL,
  "product_id" varchar(15) NOT NULL REFERENCES "foo_product" ("id") DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO "new__foo_listing" ("id", "price", "product_id")
  SELECT "id", "price", NULL FROM "foo_listing";
DROP TABLE "foo_listing";
ALTER TABLE "new__foo_listing" RENAME TO "foo_listing";
CREATE INDEX "foo_sale_listing_id_077299ff" ON "foo_sale" ("listing_id");
CREATE INDEX "foo_listing_product_id_2048e18c" ON "foo_listing" ("product_id");
--
-- Add field store to listing
--
CREATE TABLE "new__foo_listing" (
  "id" integer NOT NULL PRIMARY KEY AUTOINCREMENT,
  "price" decimal NOT NULL,
  "product_id" varchar(15) NOT NULL REFERENCES "foo_product" ("id") DEFERRABLE INITIALLY DEFERRED, "store_id" varchar(25) NOT NULL REFERENCES "foo_store" ("name") DEFERRABLE INITIALLY DEFERRED
);
INSERT INTO "new__foo_listing" ("id", "price", "product_id", "store_id")
  SELECT "id", "price", "product_id", NULL FROM "foo_listing";
DROP TABLE "foo_listing";
ALTER TABLE "new__foo_listing" RENAME TO "foo_listing";
CREATE INDEX "foo_listing_product_id_2048e18c" ON "foo_listing" ("product_id");
CREATE INDEX "foo_listing_store_id_42056821" ON "foo_listing" ("store_id");
COMMIT;

As you can see the migration first creates "foo_listing" without the ForeignKey to Product, then it creates a table "new__foo_listing" this time with the ForeignKey and then it drop the initial table (DROP TABLE "foo_listing";) and it renames the new one (ALTER TABLE "new__foo_listing" RENAME TO "foo_listing";)

Are my models complex that it requires such complex steps in the migration?

I copied your models file to my project and ran makemigration on it. It generated exactly what I would have expected to find.

I’ll go out on a limb here and say there’s some context missing with this in terms of what might have been previously existing - or perhaps the database being used. (Is this PostgreSQL, or something else?)

I tried it in the default settings with a sqlite database, might be indeed that’s the thing, I’ll test my app with postgres.

Yea, that’s it. Sqlite only supports a subset of the ALTER TABLE command - what you’re trying to do isn’t directly permitted.

Thanks for clearing it up for me!