SQLAlchemy sheme that allows two tables to share a primary key column

Question

Say I have two tables, foo and bar. Both have primary keys. I want to set it up in SQLAlchemy so that the combined set of foo.id and bar.id is unique. How would I do that?

I tried adding another table containing only the primary keys and having foreign keys in foo and bar, like so:

class foo(Base):
    __tablename__ = 'foo'
    id = Column(Integer, ForeignKey('primary_keys.id'), primary_key=True) 

class bar(Base):
    __tablename__ = 'bar'
    id = Column(Integer, ForeignKey('primary_keys.id'), primary_key=True) 

class primary_keys(Base):
    __tablename__ = 'primary_keys'
    id= Column(Integer, primary_key=True)

But it gave me this error:

FlushError: Instance has a NULL identity key. If this is an auto-generated value, check that the database table allows generation of new primary key values, and that the mapped Column object is configured to expect these generated values. Ensure also that this flush() is not occurring at an inappropriate time, such aswithin a load() event.

Is there maybe a better solution for what I'm trying to do?

EDIT: I'm using a sqlite db.

Which database is this on? I believe this should work as written with MS-SQL. Also, I assume you're using the create_all function? — user25064
I believe this should work as coded, perhaps you can provide a fully runnable example that causes the error. — user25064

gellej gellej · Accepted Answer · 2015-08-27T14:17:21

If you use postgresql or oracle the primary keys of both foo and bar can be defined to use the same sequences.

http://docs.sqlalchemy.org/en/rel_1_0/core/defaults.html#defining-sequences

table = Table("cartitems", meta,
    Column("cart_id", Integer, Sequence('cart_id_seq'), primary_key=True),
)

the sequence object would basically do what you are trying to do with your primary_keys table

SQLAlchemy sheme that allows two tables to share a primary key column

1 Answers