Issue: Unique Constraint not being applied after schema migration
Author: beerdedfellow
State: closed
Created: 2016-06-29 21:18:22

Hello,

I am building a ticket tracking application using peewee to interact with sqlite. I am defining my model as follows

``` python
class Ticket(BaseModel):
    number = CharField(unique=True)
    modified = DateTimeField()
    subject = CharField()
    queue = CharField()
    assignee = CharField(null=True)
    status = CharField()
    ismodified = BooleanField(default=False)
    newtime = DateTimeField(null=True)
    account_name = CharField(default='')
    account_number = CharField(default='')
```

I am using `SqliteMigrator` to add rows to the schema for newer versions, like so:

``` python
if schema_version < 2:
    migrator = SqliteMigrator(sqlite_db)
    account_name_field = CharField(default='')
    account_number_field = CharField(default='')
    with sqlite_db.transaction():
        migrate(
            migrator.add_column('Ticket', 'account_name', account_name_field),
            migrator.add_column('Ticket', 'account_number', account_number_field)
        )
    query = Config.update(config_value=2).where(Config.config_key == 'schema_version')
    query.execute()
```

If the migration gets executed, the unique constraint for the `Ticket.number` field no longer applies. I also noticed that it no longer creates a primary key as well (which may be the cause of the unique constraint failure):

```
sqlite> select id,number from ticket;
5|160606-11739
6|160610-11206
9|160614-06385
11|160617-08108
15|160620-10616
|160516-13419
|160516-13419
|160620-10616
```

I've re-initialized a clean database and performed the schema migration again, but unfortunately I am unable to replicate the issue at this time :-(. Are there any conditions where peewee would not create a primary key? Thanks for any help and insight you're able to provide!

NOTE: to avoid this in the future I realize that using the ticket number as the primary key is probably ideal (they will always be unique). However, is there an easy way to add the `primary_key = True` initialization argument to an existing field? I'm guessing I may need to rename it, initialize a new one, and then migrate the records to it. If so, do you have any examples of how I can achieve this? (search is failing me) 

Thanks again for your help and a great product!


I'm not able to replicate this locally. Here's the code I'm using:

``` python
import os
from peewee import *

if os.path.exists('/tmp/x.db'):
    os.unlink('/tmp/x.db')

db = SqliteDatabase('/tmp/x.db')

class Ticket(Model):
    number = CharField(unique=True)
    modified = DateTimeField()
    subject = CharField()
    queue = CharField()
    assignee = CharField(null=True)
    status = CharField()
    ismodified = BooleanField(default=False)
    newtime = DateTimeField(null=True)
    #account_name = CharField(default='')
    #account_number = CharField(default='')
    class Meta:
        database = db

Ticket.create_table()

def main():
    from playhouse.migrate import SqliteMigrator, migrate
    migrator = SqliteMigrator(db)
    account_name_field = CharField(default='')
    account_number_field = CharField(default='')
    with db.transaction():
        migrate(
            migrator.add_column('Ticket', 'account_name', account_name_field),
            migrator.add_column('Ticket', 'account_number', account_number_field),
        )

if __name__ == '__main__':
    main()
```

If I just import the module, then the DB is created and has the following schema:

``` sql
-- ran .fullschema --indent in the sqlite console
CREATE TABLE "ticket"(
  "id" INTEGER NOT NULL PRIMARY KEY,
  "number" VARCHAR(255) NOT NULL,
  "modified" DATETIME NOT NULL,
  "subject" VARCHAR(255) NOT NULL,
  "queue" VARCHAR(255) NOT NULL,
  "assignee" VARCHAR(255),
  "status" VARCHAR(255) NOT NULL,
  "ismodified" INTEGER NOT NULL,
  "newtime" DATETIME
);
CREATE UNIQUE INDEX "ticket_number" ON "ticket"("number");
```

After running the migration, I have the following schema:

``` sql
CREATE TABLE "ticket"(
  "id" INTEGER NOT NULL PRIMARY KEY,
  "number" VARCHAR(255) NOT NULL,
  "modified" DATETIME NOT NULL,
  "subject" VARCHAR(255) NOT NULL,
  "queue" VARCHAR(255) NOT NULL,
  "assignee" VARCHAR(255),
  "status" VARCHAR(255) NOT NULL,
  "ismodified" INTEGER NOT NULL,
  "newtime" DATETIME,
  "account_name" VARCHAR(255) NOT NULL,
  "account_number" VARCHAR(255) NOT NULL
);
CREATE UNIQUE INDEX "ticket_number" ON "ticket"("number");
```

Based on this quick test it seems to be working correctly. Using peewee@49eeec7c14be38f0ba21b428e94f716c45009230 with Python 2.7.12 and SQLite 3.14.


Can you provide any additional info, or try my script out on your own machine?


Please re-open if there's something I've missed or if the above code that I shared reproduces the issue on your computer.