sqlmigrate doesn't show a drop constraint SQL when previous create constrain operation wasn't perform.

[Scott Stafford]

Repro steps, using PostgreSQL and Django 2.2.4:

1. Create a new model with a normal, constrained ForeignKey, such as:

class Child(models.Model):
    parent = models.ForeignKey('myapp.Parent', related_name="+", on_delete=models.DO_NOTHING)

2. Create a migration: python manage.py makemigrations

3. Add db_constraint=False, like so:

class Child(models.Model):
    parent = models.ForeignKey('myapp.Parent', related_name="+", on_delete=models.DO_NOTHING, db_constraint=False)

4. Create a second migration: python manage.py makemigrations

5. Peep at the generated SQL. The first migration creates the constraint, and the second fails to remove it:

(.env) c:\wc\dbconstraintnotdropped>python manage.py sqlmigrate myapp 0001
BEGIN;
--
-- Create model Parent
--
CREATE TABLE "myapp_parent" ("id" serial NOT NULL PRIMARY KEY);
--
-- Create model Child
--
CREATE TABLE "myapp_child" ("id" serial NOT NULL PRIMARY KEY, "parent_id" integer NOT NULL);
ALTER TABLE "myapp_child" ADD CONSTRAINT "myapp_child_parent_id_af46d0ab_fk_myapp_parent_id" FOREIGN KEY ("parent_id") REFERENCES "myapp_parent" ("id") DEFERRABLE INITIALLY DEFERRED;
CREATE INDEX "myapp_child_parent_id_af46d0ab" ON "myapp_child" ("parent_id");
COMMIT;

(.env) c:\wc\dbconstraintnotdropped>python manage.py sqlmigrate myapp 0002
BEGIN;
--
-- Alter field submission on child
--
COMMIT;

Presumably related to the changes made on ticket:23264.

[Mariusz Felisiak]

Thanks for this report, however IMO everything works properly.

Removing constraints is based on introspection, hence SQL generated by the second migration is empty because you didn't run the first migration that creates constraint and in a consequence the second one wasn't able to introspect a non-existent constraint.

0002 - SQL (before performing the first migration)

BEGIN;
--
-- Alter field parent on child
--
COMMIT;

0002 - SQL (after performing the first migration)

BEGIN;
--
-- Alter field parent on child
--
SET CONSTRAINTS "test_30741_child_parent_id_f98c7dab_fk_test_30741_parent_id" IMMEDIATE; ALTER TABLE "test_30741_child" DROP CONSTRAINT "test_30741_child_parent_id_f98c7dab_fk_test_30741_parent_id";
COMMIT;

[Scott Stafford]

Thank you for looking into this! Curiously, I am not seeing what you are seeing, however. We encountered this bug first in normal course of usage and found that the constraint had not been removed. And, in the test example I set up for this ticket, I just now ran the first migration and then sqlmigrate-checked the second, and it still is a no-op:

(.env) c:\wc\dbconstraintnotdropped>python manage.py migrate myapp 0001
Operations to perform:
  Target specific migration: 0001_initial, from myapp
Running migrations:
  Applying myapp.0001_initial... OK

(.env) c:\wc\dbconstraintnotdropped>python manage.py sqlmigrate myapp 0002
BEGIN;
--
-- Alter field submission on child
--
COMMIT;

Then also, I ran the second migration yet the constraint remained:

(.env) c:\wc\dbconstraintnotdropped>python manage.py dbshell
psql (11.2)
WARNING: Console code page (437) differs from Windows code page (1252)
         8-bit characters might not work correctly. See psql reference
         page "Notes for Windows users" for details.
Type "help" for help.

ventus_master=# \d+ myapp_child
                                                    Table "public.myapp_child"
    Column     |  Type   | Collation | Nullable |                 Default                 | Storage | Stats target | Description
---------------+---------+-----------+----------+-----------------------------------------+---------+--------------+-------------
 id            | integer |           | not null | nextval('myapp_child_id_seq'::regclass) | plain   |              |
 parent_id     | integer |           | not null |                                         | plain   |              |
Indexes:
    "myapp_child_pkey" PRIMARY KEY, btree (id)
    "myapp_child_parent_id_af46d0ab" btree (parent_id)
Foreign-key constraints:
    "myapp_child_parent_id_af46d0ab_fk_myapp_parent_id" FOREIGN KEY (parent_id) REFERENCES myapp_parent(id) DEFERRABLE INITIALLY DEFERRED

[Mariusz Felisiak]

I cannot reproduce this issue with provided models, everything works for me. Maybe you have some local issues with constraints introspection, it's hard to tell. Please don't reopen this ticket without providing a sample project.

[Scott Stafford]

Ah, yes, it appears the issue with introspection probably has to do with our use of Postgres schemas. The table/constraint in question is in a non-public schema
think I figured out what the issue is. In our setup, we use Postgres schemas. When I set up the test example, I was lazy and used our existing database and just let it create the tables right in there -- the new parent table picked up the custom schema and that is somehow interfering. When I retested after you said you couldn't repro, I made a brand new database and then it worked as expected.

Would you still consider this a bug, or are we no longer "under warranty" using schemas like this? If yes, I can try and set up a usable reproduction project, and alter the title of the bug...

[Mariusz Felisiak]

I think it was fixed quite recently (see #30644) you can check your issue against the current master.