SQLAlchemy: How to make an integer column auto_increment (and unique) without making it a primary key?

Question

I am using Flask extension for SQLAlchemy to define my database model. I want an id column to be int type and with auto_increment property but without making it a primary key. How do I achieve it?

I tried this:

from flask import Flask, jsonify
from flask.ext.sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql://root:ajay@localhost/pydb'
app.config['SQLALCHEMY_ECHO'] = True
db = SQLAlchemy(app)

class Scenario(db.Model):
    scid = db.Column(db.Integer, nullable=False, unique=True, autoincrement=True)
    scenario_name = db.Column(db.String(100), primary_key=True)
    scenario_description = db.Column(db.String(200), nullable=False)
    image_id = db.Column(db.Integer, db.ForeignKey('images.id', onupdate='CASCADE', ondelete='CASCADE'))

    def __init__(self, scenario_name, scenario_description, image_id=None):
        self.scenario_name = scenario_name
        self.scenario_description = scenario_description
        self.image_id = image_id

    def __repr__(self):
        return '<Scenario %r, %r, %r>' % (self.scenario_name, self.scenario_description, self.image_id)

but this doesn't set the scid column as auto_increment.

possible duplicate of [Set AUTO\_INCREMENT using SqlAlchemy with MySQL on Columns with non-primary keys?](http://stackoverflow.com/questions/2937229/set-auto-increment-using-sqlalchemy-with-mysql-on-columns-with-non-primary-keys) — Martijn Pieters, Oct 08 '13 at 09:05
Then bring attention to that question, perhaps with a bounty. Your question is still a duplicate of that post; you are trying to mark a non-primary key int column as auto-incrementing. — Martijn Pieters, Oct 08 '13 at 09:18
@ajay: The other question's top answer says it all: SQLAlchemy doesn't really handle this easily. Also, [setting the `autoincrement` field has no effect for columns that are not part of the primary key](http://docs.sqlalchemy.org/en/rel_0_8/core/metadata.html#sqlalchemy.schema.Column.__init__). — Mark Hildreth, Oct 08 '13 at 09:26
So I can't have an id column to keep count of the records? I have two other int columns which together make a primary key. Do I have to forgo the auto_increment id column which I could get so easily by writing bare mysql query? — ajay, Oct 08 '13 at 09:32
ajay, this is too keep track of the record count? what if you delete records? why not use count() — Joe Doherty, Oct 08 '13 at 09:53
@Joe Yes, you are right. I can actually do away with such a column. Thanks :) — ajay, Oct 08 '13 at 10:13
Not possible. see http://stackoverflow.com/questions/18197243/auto-incrementing-a-non-unique-id-upon-creation-using-sqlalchemy — Chiedo, Jun 23 '14 at 13:59

score 1 · Answer 1 · answered Nov 25 '15 at 19:45

You can add an AUTO_INCREMENT not primary key but you can't have two AUTO_INCREMENT fields

If you don't have AUTO_INCREMENT you can add an AUTO_INCREMENT and UNIQUE whith something like this:

ALTER TABLE `items` ADD `AutoInc` INT NOT NULL AUTO_INCREMENT, ADD UNIQUE (`AutoInc`)

SQLAlchemy: How to make an integer column auto_increment (and unique) without making it a primary key?

1 Answers1