Opened 6 years ago

Closed 6 years ago

#22611 closed Bug (wontfix)

sqlclear command tries to drop non-existing constraint with ForeignKey to self (postgresql)

Reported by: valberg Owned by:
Component: Core (Management commands) Version: 1.7-beta-2
Severity: Normal Keywords:
Cc: Triage Stage: Accepted
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no


Having the following model (in an app called test_app):

class Foo(models.Model):
    bar = models.ForeignKey('self')

The sqlclear management command returns:

$ ./ sqlclear testapp
ALTER TABLE "testapp_foo" DROP CONSTRAINT "bar_id_refs_id_83e148c5";
DROP TABLE "testapp_foo";


Piping this into psql results in the following error:

> ./ sqlclear testapp | psql -U django -h localhost django
ERROR:  constraint "bar_id_refs_id_83e148c5" of relation "testapp_foo" does not exist
ERROR:  current transaction is aborted, commands ignored until end of transaction block

Checking the table in psql reveals:

django=# \d testapp_foo
                         Table "public.testapp_foo"
 Column |  Type   |                        Modifiers
 id     | integer | not null default nextval('testapp_foo_id_seq'::regclass)
 bar_id | integer | not null
    "testapp_foo_pkey" PRIMARY KEY, btree (id)
    "testapp_foo_bar_id" btree (bar_id)
Foreign-key constraints:
    "testapp_foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES testapp_foo(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "testapp_foo" CONSTRAINT "testapp_foo_bar_id_fkey" FOREIGN KEY (bar_id) REFERENCES testapp_foo(id) DEFERRABLE INITIALLY DEFERRED

So it seems that the sqlclear command generates a different name for the
constraint than it actually is. I'm guessing that postgresql is in charge of
naming the constraint. At least looking at the output of the sqlall command, there
is no indication of Django doing any naming.

$ ./ sqlall testapp
CREATE TABLE "testapp_foo" (
    "id" serial NOT NULL PRIMARY KEY,
    "bar_id" integer NOT NULL REFERENCES "testapp_foo" ("id") DEFERRABLE INITIALLY DEFERRED
CREATE INDEX "testapp_foo_bar_id" ON "testapp_foo" ("bar_id");


I've figured out that the "wrongful" naming is done in
but there my knowledge of Djangos ORM stops :)

It might be worth noting that doing a simple DROP statement drops the table
just fine. So maybe the ALTER statement isn't necessary?:

$ echo 'BEGIN; DROP TABLE "testapp_foo"; COMMIT;' | psql -U django -h localhost django

(I'm using Django 1.7b3 installed with pip install git+, but it is not listed in the version list.)

Change History (11)

comment:1 Changed 6 years ago by Claude Paroz

Triage Stage: UnreviewedAccepted

I can confirm this bug, I've encountered it while working on #19750. However, the future of 'sql*' management commands is a bit uncertain with the new migration framework. At least, they will need to be updated to use the new DatabaseSchemaEditor classes.

comment:2 Changed 6 years ago by Olivier Le Thanh Duong

Owner: changed from nobody to Olivier Le Thanh Duong
Status: newassigned

comment:3 Changed 6 years ago by valberg

olethanh: How far are you with this?

comment:4 Changed 6 years ago by valberg

Owner: changed from Olivier Le Thanh Duong to valberg

comment:5 Changed 6 years ago by valberg

Has patch: set
Patch needs improvement: set

I've made a pull request ( and would appreciate any feedback. I'm uncertain on a couple of topics:

  • The schema_editor.create_model() takes care of creating indexes, so sql_indexes() is not that straight forward to convert to using schema editor.
  • The old sql functions took the style argument, but this is not used anywhere in the schema editor.
  • The tests pass, but they probably need more improvement.
  • Currently it does not do any nice coloring/highlighting of the output. It would be nice, but is not that important in my opinion.
Last edited 6 years ago by valberg (previous) (diff)

comment:6 Changed 6 years ago by Andrew Godwin

Severity: NormalRelease blocker

I'd actually like to drop the sql* commands for apps that are marked as having migrations, as they're no longer needed, and we'll finally deprecate and remove them along with other creation-based stuff in 2.0 (they should pretty much be replaced by sqlmigrate, which obviously stays)

In particular, I think that:

  • sql
  • sqlall
  • sqlcustom
  • sqlindexes
  • sqldropindexes
  • sqlsequencereset

should be modified to error and quit if supplied with an app that's got migrations. This would leave just sqlflush and sqlmigrate working for those apps, which both make sense in the context.

comment:7 Changed 6 years ago by valberg

I have made a new PR (now following the contribution guidelines):

There are still some issues with coloring though. I will address that if the rest of the PR looks good.

comment:8 Changed 6 years ago by Andrew Godwin

Severity: Release blockerNormal

valberg has just confirmed on IRC that this is also a problem on 1.6, so as it's no longer a regression, dropping release blocker status.

comment:9 Changed 6 years ago by valberg

Has patch: unset
Owner: valberg deleted
Patch needs improvement: unset
Status: assignednew

Since this is an old bug, and the sql commands are likely to go away in future versions due to migrations, I'm stopping the work on this.

comment:10 Changed 6 years ago by Claude Paroz

#22910 was a duplicate (related to sqldropindexes)

comment:11 Changed 6 years ago by Tim Graham

Resolution: wontfix
Status: newclosed
Note: See TracTickets for help on using tickets.
Back to Top