mysql - Auto incrementing a non-unique id upon creation using SQLAlchemy -

my primary goal make implementing revision histories , journaling easier.

i found myself wondering if it's possible, using flask-sqlalchemy (or straight sql), auto incremented non-unique integer mysql. i found stack overflow post close want question focused on primary key. example, if table had these columns,

revision_id = db.column(db.integer, nullable=false) post_id = db.column(db.integer, nullable=false)  __table_args__ = (     primarykeyconstraint('post_id', 'revision_id'), ) 

would possible create new post has revision_id of 1 , post_id of max(post_id) + 1 without problem of 2 users attempting create post @ same time , creating same post_id?

the advantage of system makes post history (and differencing) simple. whenever wants modify post i'd use same post_id original , increment revision_id (which, i'm typing out, has same problem).

update:

sylvain leroux put me on right track how solve problem. needed set both tables primary key in sqlalchemy. if there's more 1 primary key in sqlalchemy doesn't assume they're unique. here's current definition,

revision_id = db.column(db.integer, primary_key=true, nullable=false, autoincrement=false, default=1) post_id = db.column(db.integer, primary_key=true, nullable=false, autoincrement=true) __table_args__ = (     primarykeyconstraint('post_id', 'revision_id'), ) 

which produces sql

create table `post` (     revision_id integer not null,      post_id integer not null auto_increment,      primary key (post_id, revision_id),  ) 

which allowed me insert , without post_id.

all credit sylvain leroux since merely translated answer sqlalchemy.

to quote classic mysql error message:

there can 1 auto column , must defined key:

auto_increment column must either (in) primary key or (in) key (also known index in mysql, may or may unique).

---

as for:

select max(id) tbl @new_id; insert tbl (id, ...) values (@new_id, ....); 

you understand if 2 concurrent requests same thing, end having 2 new rows, totally unrelated, same id. don't want use table locks avoid pitfall.

as of myself, "don't that". maybe make things more easy in application, bet made tons of other things far more complicated or much less robust.

---

but ... if real problem if keep "post id" constant, remember auto_increment part of key (http://sqlfiddle.com/#!2/9b4b45/1):

create table tbl(id int auto_increment not null,                   rev int not null default 1,                  content text,                  primary key(id,rev));  -- insert 3 new contents "auto id" , default revision 1 insert tbl (content) values ("alpha"), ("bta"), ("gamma"); 

say there error in item id2,rev1. insert new revision:

insert tbl (id, rev, content) values (2,2,"beta"); 

if want see various revision of item id2:

select * tbl id=2 order rev; 

you have find how sqlalchemy (:d), definitively possible mysql.