title: Change SQLAlchemy Primary Key after it has been defined
body: <p><strong>Problem</strong>: Simply put, I am trying to redefine a SQLAlchemy ORM table's primary key after it has already been defined.</p>
  
  <p><strong>Example</strong>:</p>
  
  <pre><code>class Base:
  
      @declared_attr
      def __tablename__(cls):
          return f"{cls.__name__}"
  
  
      @declared_attr
      def id(cls):
          return Column(Integer, cls.seq, unique=True, 
                        autoincrement=True, primary_key=True)
  
  Base = declarative_base(cls=Base)
  
  class A_Table(Base):
      newPrimaryKeyColumnsDerivedFromAnotherFunction = []
      # Please Note: as the variable name tries to say,
      # these columns are auto-generated and not known until after all
      # ORM classes (models) are defined
  
  # OTHER CLASSES
  
  
  def changePriKeyFunc(model):
      pass # DO STUFF
  
  # Then do
  Base.metadata.create_all(bind=arbitraryEngine)
  # After everything has been altered and tied into a little bow
  </code></pre>
  
  <p>*Please note, this is a simplification of the true problem I am trying to solve.</p>
  
  <p><strong>Possible Solution</strong>: Your first thought might have been to do something like this:</p>
  
  <pre><code>def possibleSolution(model):
      for pricol in model.__table__.primary_key:
          pricol.primary_key = False
  
      model.__table__.primary_key = PrimaryKeyConstraint(
          *model.newPrimaryKeyColumnsDerivedFromAnotherFunction,
  
          # TODO: ADD all the columns that are in the model that are also a primary key
          # *[col for col in model.__table__.c if col.primary_key]
          )
  </code></pre>
  
  <p>But, this doesn't work, because when trying to add, flush, and commit, an error gets thrown:</p>
  
  <pre><code>InvalidRequestError: Instance &lt;B_Table at 0x104aa1d68&gt; cannot be refreshed - 
  it's not persistent and does not contain a full primary key.
  </code></pre>
  
  <p>Even though this:</p>
  
  <pre><code>In [2]: B_Table.__table__.primary_key
  Out[2]: PrimaryKeyConstraint(Column('a_TableId', Integer(),
                                      ForeignKey('A_Table.id'), table=&lt;B_Table&gt;, 
                                      primary_key=True, nullable=False))
  </code></pre>
  
  <p>as well as this:</p>
  
  <pre><code>In [3]: B_Table.__table__
  Out[3]: Table('B_Table', MetaData(bind=None), 
                Column('id', Integer(), table=&lt;B_Table&gt;, nullable=False,
                       default=Sequence('test_1', start=1, increment=1, 
                                        metadata=MetaData(bind=None))), 
                Column('a_TableId', Integer(), 
                       ForeignKey('A_Table.id'), table=&lt;B_Table&gt;, 
                       primary_key=True, nullable=False), 
                schema=None)
  </code></pre>
  
  <p>and finally:</p>
  
  <pre><code>In [5]: b.a_TableId
  Out[5]: 1
  </code></pre>
  
  <p>Also note that the database actually reflects the changed (and true) primary key, so I know that there's something going on with the ORM/SQLAlchemy.</p>
  
  <p><strong>Question</strong>: In summary, how can I change the model's primary key after the model has already been defined?</p>
  
  <p>edit: See below for full code (same type of error, just in SQLite)</p>
  
  <pre><code>from sqlalchemy import Column, Integer, ForeignKey
  from sqlalchemy.orm import relationship, sessionmaker
  from sqlalchemy.ext.declarative import declared_attr, declarative_base
  from sqlalchemy.schema import PrimaryKeyConstraint
  from sqlalchemy import Sequence, create_engine
  
  
  class Base:
  
      @declared_attr
      def __tablename__(cls):
          return f"{cls.__name__}"
  
      @declared_attr
      def seq(cls):
          return Sequence("test_1", start=1, increment=1)
  
      @declared_attr
      def id(cls):
          return Column(Integer, cls.seq, unique=True, autoincrement=True, primary_key=True)
  
  
  Base = declarative_base(cls=Base)
  
  
  def relate(model, x):
      """Model is the original class, x is what class needs to be as
      an attribute for model"""
  
      attributeName = x.__tablename__
      idAttributeName = "{}Id".format(attributeName)
      setattr(model, idAttributeName,
              Column(ForeignKey(x.id)))
      setattr(model, attributeName,
              relationship(x,
                           foreign_keys=getattr(model, idAttributeName),
                           primaryjoin=getattr(
                               model, idAttributeName) == x.id,
                           remote_side=x.id
                           )
              )
      return model.__table__.c[idAttributeName]
  
  
  def possibleSolution(model):
      if len(model.defined):
          newPriCols = []
          for x in model.defined:
              newPriCols.append(relate(model, x))
  
          for priCol in model.__table__.primary_key:
              priCol.primary_key = False
              priCol.nullable = True
  
          model.__table__.primary_key = PrimaryKeyConstraint(
              *newPriCols
              # TODO: ADD all the columns that are in the model that are also a primary key
              # *[col for col in model.__table__.c if col.primary_key]
              )
  
  
  class A_Table(Base):
      pass
  
  
  class B_Table(Base):
      defined = [A_Table]
  
  
  possibleSolution(B_Table)
  
  engine = create_engine('sqlite://')
  Base.metadata.create_all(bind=engine)
  
  Session = sessionmaker(bind=engine)
  session = Session()
  
  a = A_Table()
  b = B_Table(A_TableId=a.id)
  
  print(B_Table.__table__.primary_key)
  
  session.add(a)
  session.commit()
  session.add(b)
  session.commit()
  </code></pre>