8

I'm not sure I've titled this question correctly. I can add a unique constraint well enough to any of my tables, but in the case below I'm not sure how to do what I'm after: 

class Branch(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(160))
    #foreign key for 'branches' in Account class. access with Branch.account
    account_id = db.Column(db.Integer, db.ForeignKey('account.id'))

class Account(db.Model):
    id = db.Column(db.Integer, primary_key = True)
    name = db.Column(db.String(160), unique=True)
    branches = db.relationship('Branch', backref = 'account', lazy = 'dynamic')

So when I added a unique constraint to the Branch table's name column, I could not add same-name branches to different accounts. For example, Seattle, could be a branch for both AccountA and AccountB.

What I want to do is apply a constraint that checks for uniqueness only when account.id is the same. Is this possible?

Chockomonkey
  • 3,895
  • 7
  • 38
  • 55
  • 1
    possible duplicate of [sqlalchemy unique across multiple columns](http://stackoverflow.com/questions/10059345/sqlalchemy-unique-across-multiple-columns) – dirn Dec 31 '14 at 18:47
  • Exact duplicate. Thanks for the link--it helped me with the solution i needed! – Chockomonkey Dec 31 '14 at 19:23

1 Answers1

7

Thanks to dirn, pointing out the duplicate, I've added: 

__table_args__ = (db.UniqueConstraint('account_id', 'name', name='_account_branch_uc'),
                 )

to my Branch class, and then pushed it to the database with alembic via:

def upgrade():
    op.create_unique_constraint('_account_branch_uc', 'branch', ['name','account_id'])

I will note, though, that since I added this constraint manually via alebmic, I'm not certain if I added it correctly to my model. I suppose I'll find out when I eventually wipe my DB and start a new one.

EDIT

I have rolled a new database and the __table_args__ from above works correctly!

Chockomonkey
  • 3,895
  • 7
  • 38
  • 55