CODEWITHSUNDEEP
pythonsqlalchemy
wfawwer
wfawwer

Reputation: 305

SQL Alchemy Composite Key Order

I want to create a composite primary key of the form (date,id). My code currently looks somewhat like this

class Gyroinfo(Base):
    __tablename__ = 'GYROINFO'

    id = Column(Integer, primary_key=True,autoincrement = True)
    date = Column(DateTime, primary_key = True)

but this defaults to a primary key of the form (id, date). How can I switch the primary key order?

Upvotes: 2

Views: 1809

Answers (1)

SuperShoot
SuperShoot

Reputation: 10872

I've assumed you are using MySQL here, so if not let me know and I'll remove this answer.

You can read what Mike Bayer has said about reordering of Primary Key fields with MySQL here. And the rational behind why SQLAlchemy behaves like this, here.

You can achieve what you want through using a PrimaryKeyConstraint and a separate UniqueConstraint on the id field. E.g.:

class Gyroinfo(Base):
    __tablename__ = 'GYROINFO'

    id = Column(Integer, autoincrement=True, unique=True)
    date = Column(DateTime)

    __table_args__ = (
        PrimaryKeyConstraint(date, id),
    )

Which produces the following sql:

CREATE TABLE `GYROINFO` (
        id INTEGER NOT NULL AUTO_INCREMENT,
        date DATETIME NOT NULL,
        PRIMARY KEY (date, id),
        UNIQUE (id)
)

Without the extra unique=True in the id field definition, SQLAlchemy emits a CREATE TABLE with the columns ordered as you wish:

CREATE TABLE `GYROINFO` (
        id INTEGER NOT NULL AUTO_INCREMENT,
        date DATETIME NOT NULL,
        PRIMARY KEY (date, id)
)

But it's rejected by MySQL:

Incorrect table definition; there can be only one auto column and it must be defined as a key

However, it begs the question as to why you need your date field in the primary key at all. As id is auto-increment, it will be unique across all entries into the table, so including date in a composite field with id adds nothing for the extra complexity. I'd stick with:

class Gyroinfo(Base):
    __tablename__ = 'GYROINFO'

    id = Column(Integer, autoincrement=True, primary_key=True)
    date = Column(DateTime, index=True)

Upvotes: 5

Related Questions