Composite Keys in Sqlalchemy

Question

I'm using flask-sqlalchemy to build a webapp and I have the following model(s):

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), unique=True)
    candidates = db.relationship('Candidate', backref='post', lazy='dynamic')

    def __init__(self, name):
        self.name = name

    def __repr__(self):
        return "<Post %r>" % self.name

class Candidate(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.String(80), primary_key=True)
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))
    hostel = db.Column(db.String(80))
    yes_no = db.Column(db.Boolean)
    #votes = db.relationship('Vote', backref='vote', lazy="dynamic")

    def __init__(self, name, hostel, post_id, yes_no):
        self.name = name
        self.hostel = hostel
        self.post_id = post_id
        self.yes_no = yes_no

    def __repr__(self):
        return "<Candidate: %r, Post: %r>" % (self.name, self.post.name)

I'm totally noob when it comes to database designing, so I'll really appreciate a few pointers about some fundamental queries I have

• Is the id column necessary? Most of the tutorials I've seen have that as a column(primary key specifically). Can I do without it? Especially in cases where I have another primary key?

• How can I have a composite key constraint on Candidate#Name and Candidate#post_id columns, given that post_id is a foreign key.

• How can I "refresh" my database. After I've made changes in the model, how can I make sure those changes are reflected in the actual tables? Do I need to drop_all and create_all manually?

• Lastly, does the sqlalchemy documentation apply to flask-sqlalchemy as well?

Answer 1

Is the id column necessary? Most of the tutorials I've seen have that as a column(primary key specifically). Can I do without it? Especially in cases where I have another primary key?

For the post table, yes. For the other one, no. Some might argue that since name is unique, make it a primary key, but that would mean use the name anywhere you need to reference that post, which means more space (length of the name vs. int) (not necessarily a problem) and think about "what if the candidate name changes?". However, you certainly don't need both.

How can I have a composite key constraint on Candidate#Name and Candidate#post_id columns, given that post_id is a foreign key.

How you are doing it, every post is assigned to one single post:

candidates = db.relationship('Candidate', backref='post', lazy='dynamic')

So, the field should not be called candidates but candidate to make sense.

To answer your question, I will quote from the docs:

To specify multiple columns in the constraint/index or to specify an explicit name, use the UniqueConstraint or Index constructs explicitly.

You would need to add this to your model definition:

__table_args__ = (
        UniqueConstraint("id", "candidate_id"),
    )

Although I think it's useless here (why do you need it? if id is unique(primary key), then any other combination containing it will be unique)...

How can I "refresh" my database. After I've made changes in the model, how can I make sure those changes are reflected in the actual tables? Do I need to drop_all and create_all manually?

You need to commit your changes to the database. From flask-sqlalchemy's documentation:

db.session.add(post)
db.session.commit()

Lastly, does the sqlalchemy documentation apply to flask-sqlalchemy as well?

NOTE This is what I found out on my own, no "official" source, just by reading flask-sqlalchemy's source.

Yes, what differs, is the way you access the model and the session and the engine and ... For example, in sqlalchemy:

from sqlalchemy.orm import sessionmaker
Session = sessionmaker()
session = session()
session.commit()

And in flask-sqlalchemy:

db.session.commit()

And for "Models":

# In sqlalchemy
Base = declarative_base()
class Post(Base):
    # ...

# In flask-sqlalchemy
class Post(db.Model):
    # ...

etc...

Anyway, what you do in the sqlalchemy way, you do it once, and flask-sqlalchemy does it for you in the background.

Hope it helps :) (and that's much more than one question about composite keys in sqlalchemy...)