python - Having two serial keys in postgresql via sqlalchemy -

i have unusual challenge. i'm modifying table able join 2 other legacy groups of postgresql tables.

one group pretty requires each record in table have unique integer. so, following field definition work:

numeric_id = sql.column(sql.integer, primary_key=true)

the other group of tables use uuid fields expected join requests. following field definition work:

uu_account_id = sql.column(uuid(as_uuid=true), primary_key=true)

but, clearly, can't have 2 primary keys. 1 of them needs not primary key. nice have both still automatically assigned when new record made.

any suggestions?

i'm sure can quick hack, i'm curious if there nice clean answer.

(and no: changing other tables not option. way legacy code.)

make uuid column primary key, usual.

define other column having serial type , unique. in sql i'd write

create table mytable (     mytable_id uuid primary key default uuid_generate_v4(),     mytable_legacy_id serial unique not null,     ... other cols ... ); 

so need sqlalchemy equivalent, whatever is, of not null, unique field.

note "serial" shorthand for

create sequence tablename_colname_seq; create table tablename (     colname integer default nextval('tablename_colname_seq'),     ... cols ... ); alter sequence tablename_colname_seq owned tablename.colname; 

so if can't make sqlalchemy recognise can have serial field isn't primary key, can way instead.