Sqlite and unique_together

[anonymous]

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>
    execute_manager(settings)
  File ".../virtualenv_packages/django/trunk/django/core/management/__init__.py", line 359, in execute_manager
    utility.execute()
  File ".../virtualenv_packages/django/trunk/django/core/management/__init__.py", line 304, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  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
    runfunc(klass.orm)
  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 :)

[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.

[andrew]

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.

[anonymous]

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

[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 :)

[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.

[anonymous]

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

create_unique(table, (col1, col2))

to

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

Little bit weird, isnt it? :)

[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).

[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

[andrew]

OK, looks good - applied as [c92d62e5e5df].