Ticket #144 (closed defect: fixed)

Opened 6 years ago

Last modified 5 years ago

Sqlite and unique_together

Reported by: anonymous Owned by: andrew
Priority: major Milestone: 0.7.3
Component: databaseapi Version: 0.5
Keywords: Cc:


unique_together uses SQL table constraints.

Sqlite supports table constraints, but only when creating a new table. Sqlite's ALTER TABLE statement doesn't support ADD CONSTRAINT. ( http://www.sqlite.org/omitted.html )

SQL that Django creates for creating tables with constraints in sqlite:

CREATE TABLE "myapp_mymodel" (
    "id" integer NOT NULL PRIMARY KEY,
    "a_id" integer NOT NULL REFERENCES "myapp_a" ("id"),
    "b_id" integer NOT NULL REFERENCES "myapp_b" ("id"),
    UNIQUE ("a_id", "b_id")

Sqlite's CREATE TABLE supports the UNIQUE constraint, however:

ALTER TABLE "myapp_mymodel" ADD CONSTRAINT "myapp_mymodel_a_id_27e91b9e" UNIQUE ("a_id", "b_id")

raises an exception. Traceback:

  File "<string>", line 1, in <module>
  File "manage.py", line 22, in <module>
  File ".../virtualenv_packages/django/trunk/django/core/management/__init__.py", line 359, in execute_manager
  File ".../virtualenv_packages/django/trunk/django/core/management/__init__.py", line 304, in execute
  File ".../virtualenv_packages/django/trunk/django/core/management/base.py", line 195, in run_from_argv
    self.execute(*args, **options.__dict__)
  File ".../virtualenv_packages/django/trunk/django/core/management/base.py", line 222, in execute
    output = self.handle(*args, **options)
  File ".../virtualenv_packages/south/south/management/commands/migrate.py", line 84, in handle
    skip = skip,
  File ".../virtualenv_packages/south/south/migration.py", line 526, in migrate_app
    result = run_forwards(mapp, [mname], fake=fake, db_dry_run=db_dry_run, silent=silent)
  File ".../virtualenv_packages/south/south/migration.py", line 320, in run_forwards
    silent = silent,
  File ".../virtualenv_packages/south/south/migration.py", line 266, in run_migrations
  File ".../my_project/work/simplegifts/mainsite/roles/migrations/0001_initial.py", line 41, in forwards
    db.create_unique('roles_rolesetalternative', ['roleset_id', 'alternative_id'])
  File ".../virtualenv_packages/south/south/db/generic.py", line 346, in create_unique
    self.execute("ALTER TABLE %s ADD CONSTRAINT %s UNIQUE (%s)" % (qn(table_name), qn(name), cols))
  File ".../virtualenv_packages/south/south/db/generic.py", line 70, in execute
    cursor.execute(sql, params)
  File ".../virtualenv_packages/django/trunk/django/db/backends/util.py", line 19, in execute
    return self.cursor.execute(sql, params)
  File ".../virtualenv_packages/django/trunk/django/db/backends/sqlite3/base.py", line 193, in execute
    return Database.Cursor.execute(self, query, params)
OperationalError: near "CONSTRAINT": syntax error

Solutions that come to mind for migrating unique_together in sqlite:

  • Backup, drop, recreate and repopulate the table.
  • Ignore unique_together migrations for sqlite.

Hopefully a better solution will be thought of :)


south-add-unique_together-support-for-sqlite3.patch (4.9 KB) - added by iksaif 5 years ago.

Change History

comment:1 Changed 6 years ago by fred@…

For development purposes, I added an empty definition of "create_unique" to the SQLite3 implementation of DatabaseOperations?. This is the "ignore unique_together migrations" solution.

comment:2 Changed 6 years ago by andrew

  • Status changed from new to assigned
  • Version set to 0.5
  • Milestone set to The Future

Yes, this is the same story as #52 - it _can_ be done, but via a series of workarounds (and at least in the other cases it needs an SQL parser). I'll implement a dummy set of unique functions for now for SQLite, and push this to The Future.

comment:3 Changed 6 years ago by anonymous

After fooling around with trying to fix this, I'm convinced to switch to postgres/mysql for development :o

comment:4 Changed 6 years ago by andrew

Yeah, it's not easy. SQLite's a great limited-use database, but some of the barriers it presents make it annoying for continued development...

Still, these will get fixed eventually, since it's possible :)

comment:5 Changed 5 years ago by Vadim Fint

Why not just add unique index to existing table?

CREATE UNIQUE INDEX <name> ON <table> (<col1>, <colN>, ...)

Final result will be the same as creating table with UNIQUE clause.

And even they look different:

sqlite> .schema t
CREATE TABLE t (t1 int, t2 int, UNIQUE (t1, t2));

sqlite> .schema tt
CREATE TABLE tt (t1 int, t2 int);
CREATE UNIQUE INDEX uni on tt (t1, t2);

They ARE the same except 1 thing: you need to give a name for unique index.

comment:6 Changed 5 years ago by anonymous

Hmm... as of now that can be accomplished by replacing

create_unique(table, (col1, col2))


create_index(table, (col1, col2), unique=True)

Little bit weird, isnt it? :)

Changed 5 years ago by iksaif

comment:7 Changed 5 years ago by iksaif

I attached a patch that should work. At least it works for me. I tested it on ~10 models when switching from UNIQUE on a single field to UNIQUE on two field (including the old one).

UNIQUE constraint are now correctly added/removed (single, or multi-valued).

comment:8 Changed 5 years ago by rachat de credit

I have the same opinion with most of your points, but a few need to be discussed further, I will hold a small talk with my buddies and perhaps I will look for you some suggestion shortly.

  • Henry

comment:9 Changed 5 years ago by andrew

  • Status changed from assigned to closed
  • Resolution set to fixed
  • Milestone changed from The Future to 0.7.3

OK, looks good - applied as [c92d62e5e5df].

Note: See TracTickets for help on using tickets.