Django unique=True not working

Question

This is from django's documentation:

Field.unique

If True, this field must be unique throughout the table.

This is enforced at the database level and by model validation. If you try to save a model with a duplicate value in a unique field, a django .db.IntegrityError will be raised by the model’s save() method.

Here is my models.py

class MyModel(models.Model):
    # my pk is an auto-incrementing field
    url = models.URLField("URL", unique=True)
    text = models.TextField(max_length=1000)
    # my model is just two fields, one pk (unique), and another unique field, 
    #, the url

Here my is manage.py sqlall (I ran syncdb)

CREATE TABLE `MyModel_mymodel` (
    `id` integer AUTO_INCREMENT NOT NULL PRIMARY KEY,
     `url` varchar(200) NOT NULL UNIQUE,
     `text` varchar(1000) NOT NULL,

However, in the manage.py shell, I can freely do this:

>>> from MyModel.models import MyModel
>>> MyModel().save() # it works fine!? Not even the text was checked for!
>>> MyModel(url="blah").save() 
>>> MyModel(url="blah").save() # it still works!

# I checked the mysql database afterwards, the models were saved just fine, they
# however did have different PK's (auto incrementing fields).

I'm using mysql, django 1.5. Does anyone have an idea what could possible be causing this?

I am using a custom manager, but I doubt that's the issue.

Thanks.

Answer 1

For django 1.9+
Running makemigrations then migrate applies the unique constraint to sqlite3

For django < 1.9
Since you are using django 1.5, this solution will apply.

If you added the unique=True after the table was already created, then even if you do syncdb later, the unique condition will not be added to your table.

I can confirm with sqlite3 that Django 1.5 happily saves duplicate objects with MyModel(url="blah").save() if the unique constraint does not exist in the database, which seems to contradict with the docs.

The best solution for you is to create the constraint manually in your database using this command.

ALTER TABLE MyModel_mymodel ADD UNIQUE (url); 

Or if you don't mind, you can recreate your table. (Drop the table and then run syncdb.)

Answer 2

Running sql scripts directly on the db can be avoided. Rather add the sql execution in your migration:

from __future__ import unicode_literals
from django.db import migrations, connection


def alter_table(apps, schema_editor):
    query ="ALTER TABLE <your table> ADD UNIQUE (unique_col);"
    cursor = connection.cursor()
    cursor.execute(query)
    cursor.close()

class Migration(migrations.Migration):

    dependencies = [
        ('app', 'yourlastmigration'),
    ]

    operations = [        
        migrations.RunPython(alter_table),
    ]